Configure map of equivalent DB users during compare

nkilzer

Hi,

Using SQL Server 2008/SQL Compare Pro 9.0.0.79

I've got what's obviously a bit of a unique situation here. I have three database environments; call them 'Dev', 'Stage', and 'Prod'. For various reasons, each of these environments exists completely isolated from one another (they are in separate Windows domains, on separate network segments, etc.). I can generate SQL Compare snapshots in any environment, and move them to different environments, but cannot perform direct comparisons.

Due to the environments being isolated, we have separate DB users [all the applications use integrated authentication] on each instance of the database server. Each database has equivalent users for applications (the names vary between environments for several reasons, but the permission levels are equal) who are permitted to run stored procs and some select-queries, some DBO-type users who manage the DB development, and then of course the DBA-type users who manage the environments.

The DBO/DBA-users are easy to ignore, because they have full DB rights, and can do anything anyway (so there are no explicit permissions for them). The application-users have explicit grants on objects as part of the database design as defense-in-depth against SQL-Injection and the like. Managing that list of explicit grants is sometimes an issue - occasionally a dev forgets to put the grant in a change script, and an application breaks.

What I'm looking for is a project-option to set up a map of DB users that we consider equivalent between the two databases, and have SQL Compare use that as a general substitution map. Anywhere in DB [P] that says 'PROD\Prod-App1-User' should be considered equivalent to DB using 'STAGE\Stage-App1-User' during a compare. This includes grant/deny statements on objects, the user comparison list, etc. Schema names shouldn't be mapped, nor should just arbitrary text references. The ideal case would include it identifying that an application user did not exist in the remote side, and whether it should generate a CREATE for one, while adding it to the map - it should ask what the name should be; account names are controlled by IT/Operations, so not generally up for discussion.

One other nice-to-have would be if it could generate a changescript for each of a number of maps. Several of our databases are instances of the same database schema, just with different users, and different data.

I've tried the owner mapping feature in SQL Compare, but as my application users don't actually own any objects or match a schema, they're not available in the list of users to map. I have not tried creating schema for them yet, as I'd prefer not to have an empty schema for every application - some helpful dev or tool would just remove it one day.

I'm open to other suggestions about managing this - we've thought about using Application-Roles, but we have a lot of code and databases dependent on the way things work now, and it would be nice to have a better way to manage them as well.

That got a bit long. If you got to this point, thanks.
-- Nate

peter.peart

Hmmm... tricky one. There certainly isn't anything I'm aware of within SQL Compare that would enable you to manage this, it would be a feature request.

We've already got one which is to "Create new users in destination server's default domain" under reference SC-1109, however I guess you'd like to go one step further really and map users together based on the user name rather than the domain?

Pete

nkilzer

Yes. Our operations group wants tight control over which accounts exist where, and what they can access - so autocreating users is right out.

Operations also manages the user accounts as a flat namespace (e.g. in addition to the domain, all Staging accounts start with 'Stage-'), so just eliminating the domain from the comparison isn't enough. The DB User being mapped is sufficient - though if it worked at the server level as well, that would be a nice-to-have.

Our company has a support contract, so if this needs to be made into a feature request through a different channel, I'm happy to write it up formally. It might take me a couple days to find time to list out the user stories and get the support contract details from the keeper of such things.

If it helps add priority points, I would expect that anyone using cloud-hosting for databases would find this feature useful as well. At least at one hosting provider (long in the past - I no longer remember the vendor), the user accounts were domain accounts assigned by the hoster, and matching the domain in a development environment was next to impossible.

Thanks,
-- Nate

David Atkinson

Having a formal write up would be useful, although please keep it concise. It's an issue we would like to address in a future release if we can find an elegant way of doing so.

The traditional way of solving this it by using the 'Ignore permissions and role memberships' option in SQL Compare and dealing with users and permissions by running a separate script post deployment. I guess you would need to maintain a different permissions post-deployment script for each environment you need to target. Is this something that could work for you?

David Atkinson
Product Manager
Red Gate Software

peter.peart

Hi Nate,

Did you want to send that formal write up to support(at)red-gate.com, with a subject of F0052578? We've already got a support ticket open for you under that reference and I can then forward on the write up accordingly.

Pete