comparing/sync security objects: user/role/schema

noobix

Hello,

at the moment I'm evaluating your current SQL Compare release.

First I used the function "Export data source" to export our production DB to a sql script folder structure. Everything worked as expected though I noticed that the export feature created some production server specific role/user/schema scripts in the \Security subfolder.

eg. create login [srvlive\aspnet] from windows...
create user [srvlive\aspnet] for login [srvlive\aspnet]...

I supposed there could be some trouble when trying to compare/synchronize my local development DB using the generated production scripts. I was right because the prod. security scripts cannot be sync on my development machine/sql server due to missing/different windows credentials.

On a typical development machine should be something like:
create login [devwk1\aspnet] from windows...

In other words we use different DB users/logins in the development and production environment.

I forgot to mention that I tried to compare with and without the options "Ignore permission" and/or "Ignore users' permissions and role memberships" but there was no difference.

Further I added some T-SQL code to the generated security scripts to make the "create..." statements more dynamic depending on the running machine. Minutes later I figured that was not a good idea (even broke the comparison process) due to this code is not executed against the DB engine.

What is the best approach to solve this kind of problems in SQL Compare?
Maybe you can show me a way to build a multi-server security script that enables me to compare and sync between different security environments.

We think SQL Compare could be the appropriate tool to help us with our database change management but we have to find a way to solve this SQL Server security issue.

Best regards,
Tino

Michelle T

If you don't mind losing the users and their permissions, you can work around the problem.

One way to do it is: instead of using 'export data source', create an empty folder and synchronize the live production DB into it, with the users filtered out, the option 'Ignore users' permissions and role memberships' selected, and ignoring dependencies on the appropriate wizard step. (In v7 you won't have to ignore dependencies to do this, but v6 has a bug whereby users are picked up as dependencies for things that they have permissions on even when the permissions aren't being synchronized.)

Another option is when synchronizing to the live database from the full exported scripts, filter out the users and pick the option 'Ignore users' permissoins and role memberships', and ignore dependencies. (again, you won't have to ignore dependencies in v7.)

We've been looking for a way to make synchronizing between different security environments easier, but we also don't want to arbitarily change your users' logins without adequate warning. Do you have any suggestions about how we should present an option to change the domain/machine name in users on synchronization? Would such an option solve the problems you're having?

noobix

Hi Michelle,

thank you for the solution. I was able to sync the prod. DB without the users/memberships although after that I lost - as you mentioned - the security permissions.

This is certainly a better aproach to export a live DB than using the application's "Export data source" feature. A nice-to-have feature extension for upcoming releases would be a filter option (similar to the existing one) that makes it possible to select the object types to include in the export.

Regarding the possibility to change the security permissions after sync. I would suggest some kind of sync. event system (before-sync, after-sync, etc.). This could be an useful mechanism to highly customize the sync. process. That way a developer could write some custom T-SQL scripts and hook them into the event system when needed. As a matter of course those scripts must be executed against the DB engine at the appropriate time.

For example I'd write a custom script to dynamically add/create the appropriate user(s)/login(s)/permission(s) depending on the server that's being sync.

Please let me know what you guys think about this suggestion.

I assume as with SQL Compare 6 is not possible to run some custom T-SQL script (against the DB engine) after the sync., is it?

Tino

Michelle T

We've been thinking about pre- and post- synchronization scripts, but we're not sure what people could get out of them that they couldn't easily do with:

1) just manually running things in Management Studio before and after pressing the finish button on the synchronization wizard

or

2) saving the synchronization script and prepending / appending scripts to it, then running the combined version

or

3) creating a batch file which runs the scripts before and after using the command line SQL Compare synchronization.

The only thing we could think of is that it would be nice to be able to associate the scripts with a project if you were running something repeatedly, which we aren't sure is in enough demand to justify cluttering the user interface with yet another option.

What are the advantages of pre- and post- synch scripts within SQL Compare from your perspective? Or are you looking for more control over the process, i.e. being able to inject your own code in the middle of the SQL Compare synchronization transaction (e.g. to fix the users before they are used in permissions)?

Michelle T

I've noted your feature request for filtering in the Export Data Source function and put it in our database for future consideration.

noobix

1) just manually running things in Management Studio before and after pressing the finish button on the synchronization wizard

This is not an option for us due to our batch file based deployment process.

2) saving the synchronization script and prepending / appending scripts to it, then running the combined version

This is a possible way to add custom code to the generated script.

3) creating a batch file which runs the scripts before and after using the command line SQL Compare synchronization.

I think this is the way to go for us, this option might give us the most flexibility. I just have to figure out how to use the command line version of SQL Compare. Does it offer all the features the UI version does?

As for your question about the pre-/post-sync script advantages I'd say you're right when saying "...more control over the process...". This could be the main benefit (besides more flexibility) of an event-driven system.

Michelle T

You can pass a project you've created using the UI to the command line, so you can still set everything up in the UI if you're more comfortable with that interface.

The command line does allow you to do directly from the command line everything that the UI does (try running 'SQLCompare.exe /? /v | more' for full detail), but complicated combinations of selected and deselected objects can be awkward to do without a very long command line / writing an XML argfile.

So your feature request is 'ability to insert custom elements at any point in the synchronization' rather than just 'ability to associate pre and post synch scripts with a project to be run before and after synchronizing databases with that project', then? (Just trying to get the idea straight so I can put it in the feature request list.)

noobix

"At any point in the sync." would be nice to have. Anyway I guess for the most scenarios a before-/after-sync event should be enough.

For now I should stick with the command line version, integrate it in our DB deployment scripts and run some custom scripts (security/permissions) after the sync. Is that what you'd recommend me?

By the way, does the command line tool return a DOS errorlevel?
Is there any documentation for that (and the command line features) excepting "sqlcompare.exe /?"?

Michelle T

There's some web help at http://www.red-gate.com/supportcenter/L ... %20Compare which covers the basics of using the command line, but it mostly refers you to the inbuilt command line help for more complicated questions.

Yes, I'd recommend using the command line and running the custom scripts before and after the call to the command line, that sounds like the best fit for your requirements.

The command line certainly gives a return value (I'll have to look things up to see if that's the same as a DOS errorlevel, I'm afraid) - the possible values are listed at the top of the verbose command line help given by 'sqlcompare.exe /? /v' (you probably want to send this to a text file or pipe it through more, as the scrollback on your command prompt will probably give out before you get to the section on return codes).