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.
I have written a sample 'complete list' script, below, that defines permissions in XML, which can then be audited or applied to a database.Permalink
On October 8, 2011, I presented a session on agile database development at SQL Saturday 92. The goal was to help people understand that agile database development makes your business more successful, if you adopt best practices and handle the different risks.
My presentation materials are below. Please note that the demo scripts are designed to work with the AdventureWorks database. Some of them (the design demos) will work in almost any database. Others (the unit test demos) are schema specific.
This includes several useful integrity checks, such as identifying the following:
This includes several deployment best practices, including:
This includes several examples of how to unit test database objects, including: