During SQL Saturday 92 I was asked about how to do easily manage permissions. I am assuming that systems are using application-specific database roles. Database roles are great because they are login-independent and can work in any environment.
I have found that two approaches tend to work quite well.
- Script out the necessary permissions per object (table, view, stored procedure). Put it into the same file as the object definition itself, in source control
- Have a ‘complete list’ script that lists all permissions, and applies any updates or changes.
I have written a sample ‘complete list’ script, below, that defines permissions in XML, which can then be audited or applied to a database.