SQL Server Performance Forum – Threads Archive

Verify permissions on db objects after migration

We have recently (May, 2006) migrated a production database from SQLServer 2000 to 2005. Now we are finding that permissions on stored procedures seem to have "changed". For example, a stored procedure that was created in Jan. 2005 disappeared after from the list of stored procedures in Management Studio. The DBA for the production environment claims the permissions were changed. I would like to write a query to compare existing permissions between the development database and the production database. We have also noticed missing foreign keys. I was thinking of something along the lines of SELECT * FROM sys.objects WHERE is_ms_shipped <> ‘True’
order by type, name but I don’t know where to find the permission information. Thank you

REfer to the KBAs:
http://support.microsoft.com/kb/168001/ http://support.microsoft.com/kb/907284 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Thank you, Satya. We have looked at these articles previously when we did the original migration in May. These problems are now appearing in mid-July on a database that does thousands of transactions per day. In fact, the permissions on the most recent defective object seem to have changed "spontaneously" according to the production DBA sometime after 15 July. THat is why we are looking for a run-time comparator solution. I am now evaluating SQLCompare as a quick alternative. Regards.

]]>