Baselining and Version Control

djinni69

Hey Gang - Love the Red Gate tools! I do have a question regarding permissions though. I know that SQL Compare will report differences in users and database roles, but I cannot seem to find an option to compare changes of permissions on an object level. For instance, if EXE rights changed on a stored procedure after deploying new code, can I get SQL Compare to reveal those differences?

To be familiar with our usage of SQL Compare, here's my procedure:

1 - Baseline the database and save as a snapshot using SQL Compare
2 - Deploy new code
3 - Run comparison between baseline and new database version
4 - Print and verify all differences
5 - Create a new baseline

When following this procedure, however, I am not made aware of object level permission changes. I can do it (tediously) by using sp_helprotect, but I'd really love to use SQL Compare as one stop shopping Any suggestions?

If this isn't possible in the latest version, can we get it included in an update or next version?

Thanks,
Brett

rgribble

Make sure you DONT have the following options set:

- Ignore Permissions

- Ignore User's Permissions and Role Membership


We use SQLCompare all the time to verify permissions havent been changed. This is made alot easier if you can use Roles to assign permissions to DB objects... otherwise the users from your testing/staging environment wont be the same as the users in production, and this will cause a lot of differences, if individual users have access permissions granted.

djinni69

Forgot to post how I resolved my issue. It's user error I didn't scroll to the bottom of each table difference, otherwise I would have noticed that the difference in the tables were the GRANT permissions. DUH!!