Not comparing GRANT REFERENCES on CONTRACT

dunos

Hi,

I have two databases created from the same script file. Against one of the databases I execute the following to set up query notification:

GRANT CREATE PROCEDURE to [sql_dependency_starter]
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
to [sql_dependency_starter]
GRANT VIEW DEFINITION TO [sql_dependency_starter]

When I compare the two databases SQL Compare 8.2.0.16 notices there are differences in the role permissions. SQL Compare comes back with:

GRANT CREATE PROCEDURE TO [sql_dependency_starter]
GRANT CREATE QUEUE TO [sql_dependency_starter]
GRANT CREATE SERVICE TO [sql_dependency_starter]
GRANT VIEW DEFINITION TO [sql_dependency_starter]

What happened to the GRANT REFERENCES on CONTRACT?

Thanks

dunos

In addition SQL Packager exhibits the same behaviour in that it doesn't script the GRANT REFERENCES ON CONTRACT.

Brian Donahue

Hi Dan,

When you use SQL Compare to migrate a user or role on its' own, the permissions that are carried over are only the database-level permissions.

To apply permissions that are granted or denied to specific objects, you would migrate that object, in this case, the CONTRACT object.