Enhancement request - Syncing permissions

andersom

We have some databases we keep in sync using SQL Compare, however we do not sync the DB users on the destination database.

We currently use the /options:iu,ip in order to ignore users and prevent permissions updates from failing due to noexistant users.

That said, we do sync roles. The problem is that the ip option prevents SQL Compare from updating role permissions on the destination database.

My enhancement request is this: seperate the ip option into two options; 'ignore user permissions' and 'ignore role permissions'. This would allow us to keep our database copies user-free, but maintain role security synchronicity.

Michelle T

iu should also ignore user permissions - is it not doing so? It's meant to ignore the object permissions of users and the role memberships of users. You should be able to get the result you desire by just using iu, without using ip with it.

If that doesn't work for you, that probably means that the iu option isn't working properly and we'll look into it further.

andersom

No, iu is working just fine, the issue is that I want to be able to ignore only user permissions and not role permissions. Currently iu ignores all permissions, so I don't have that flexibility.

Michelle T

iu - Ignore Users' Permissions And Role Memberships - should only be ignoring permissions held by users, not by roles.

ip - Ignore Permissions - will ignore all permissions.

Are you finding that just iu on its own is ignoring permissions held by roles?

andersom

No, I'm finding that ip is ignoring permissions granted to roles. iu is working just fine.

Michelle T

If you just want to ignore user-held permissions, you shouldn't need to use ip at all - just using iu should ignore user permissions for you. Do you have problems synchronizing if you just use /options:iu rather than /options:iu,ip?

andersom

I'm going to feel rather foolish if the solution to my issue is already in place and that simple. Let me give it a try and get back to you ;-)

andersom

Nope, excluding the ip doesn't work. See the following excerpt from the log:

Altering permissions on [dbo].[SomeTable]
Msg 15151, Level 16, State 1, Server XXXXX, Line 1
Cannot find the user 'SomeUser', because it does not exist or you do not have permission.

See, because the users don't exist on the destination, omitting ip results in permission setting failing, causing the entire process to fail.

Michelle T

The following works for me:

1) Create a database with a user and a table, give the user a permission on that table
2) Create another database with a table with the same name but an extra column, with no users / permissions
3) Synchronize the table with iu (and without include dependancies - include dependancies picks up the user as a dependancy even though the permission isn't going to be synchronized)
4) The table synchronizes correctly, with no 'altering permissions' step and no error.

Does this work for you? If so, can you work out what the difference is between that case and the case you're having problems with? (If you can send me some more information - ideally a snapshot of your database, but just the definition of the table that's failing may well help me track down the problem - then I'll try to work out what it is about your table that is causing the iu option to not work correctly.)

andersom

Ah, I believe I've solved my problem. The issue was that I did not want users or logins synced to the destination DB, but do want roles and role permissions synced. When I executed the sync command using /options:d,iu, the "include dependancies" inherent in the "d" option was what was messing things up. I changed my options to if,iw,ifg,iup,iweo,iu,irpt and everything seems to be working properly now.

For reference, my full command-line is thus:

"E:\Red Gate\SQL Compare 6\SQLCompare.exe" /verbose /exclude:user /exclude:schema /exclude:StoredProcedure:sp_MS* /options:if,iw,ifg,iup,iweo,iu,irpt /server1:x /server2:y /database1:z /database2:z /sync

This results in syncing tables and roles, and granting permissions to the roles, but excludes users and logins as desired.

Michelle T

Glad to hear your problem's sorted - hopefully at some point we'll get the dependancies support fixed so that it takes account of what options you've selected.

Eddie D

I am pleased to inform you that we have fixed this problem in the recently released SQL Compare V.7.

If you have a valid Support & upgrades option, you can download SQL Compare V.7 using the 'Check for updates' mechanism (SQL Compare GUI ->Help ->Check for updates)

or download using this link: HERE

SQL Compare V.7 will install along side any previous versions of the software.

Many Thanks
Eddie Davis