Verify permissions on db objects after migration | SQL Server Performance Forums

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: fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
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.