Problem
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them? I'm trying to audit permissions within the database itself.
Solution
The answer on how to do this depends on what version of SQL Server you are using. SQL Server 2005 introduced a new security model built around the concept of securables and while the old security tables were retained from 2000, they only report information that you would see in a SQL Server 2000 server. That means any of the newer securables, like schemas, certificates, asymmetric keys, even the database itself, will not be reported if you use the old SQL Server 2000 tables in SQL Server 2005 or above. So let's start with SQL Server 2000.
