Options
Compare Schemas permissions
laflak
Posts: 4
Hi,
I'm running SQL 2005 and I have set some permissions on schema object.
I.E: Grant the select permisision to a role for object of the schema dbo.
Instead of setting the select permission on each object of the dbo schema.
When I use SQL Compare 6.1 to analyse my DEV instance with my QA or PROD instance, it doesn't show me the the various permission set on schema object, It only shows me something when a schame exists in one instace and not in the other.
Did I miss something or is this a normal behaviour ?
thank for your reply
I'm running SQL 2005 and I have set some permissions on schema object.
I.E: Grant the select permisision to a role for object of the schema dbo.
Instead of setting the select permission on each object of the dbo schema.
When I use SQL Compare 6.1 to analyse my DEV instance with my QA or PROD instance, it doesn't show me the the various permission set on schema object, It only shows me something when a schame exists in one instace and not in the other.
Did I miss something or is this a normal behaviour ?
thank for your reply
Comments
If you have granted or denied permissions on any database object, and you don't have the option 'Ignore Permissions' selected, you should see the permissions on the object when you select it, and differences in these permissions should make the object show up as different.
Redgate Software
'GRANT SELECT ON SCHEMA::dbo TO someRole'
And I only see schema object in the comparison, when the schema exists in one db and not in the other one.
e.g. Schema "Report" exists in DB1 with Select for Role1 and doesn't exist in DB2. SQL compare shows me correctly the difference.
On the other hand, if the schema "Report" exists on both base DB1 and DB2 but with or without different permission set, SQL compare doesn't shows me schema at all.
I'm expecting to see schema at least under the identical objects group.
Here are the options I set for the comparison:
Behaviour: "Force column order" and "Do not use transactions in synchronisation script" are activated.
Ignore: "whitespace", "Fill factor and index padding" and "collations" are activated.
I can also mention that all objects are activated in the filter objects pane.
I don't suppose you could send a pair of snapshots (or backups) for the databases in question, so I can have a look and see what's different between my simple case and the case that isn't working? My email is michelle.taylor@red-gate.com if you want to send more information in private.
Redgate Software
It appears that SQL Compare doesn’t compare permissions on dbo (or indeed any of the properties of any system objects apart from the built-in roles, which SQL Server doesn’t flag as system objects)
An options to allow this kind of comparison will maybe put in place in futur release.