Compare Schemas permissions

laflak

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

Michelle T

I'm not sure I quite understand exactly what permissions you are setting. Could you provide an example SQL statement to show what you're doing, e.g. 'GRANT SELECT ON SCHEMA::dbo TO someRole'?

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.

laflak

Yes it's exactly what I did:
'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.

Michelle T

I've just made a couple of databases with a schema and a role in each, one of which has the role with select permission on the schema, and the schema shows up as different for me, with the options you gave.

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.

laflak

After some exchange of email wich Michelle Taylor.
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.