A person with a small amount of SQL Server DBA experience came to me and asked if there was an automatic job deletes users or changes user permissions. I told them there was no default job that does that. I then suggested they look at the audits to see if there was any activity. Come to find out, there were no audits on the installations. I decided to implement some audits so the question I was originally asked could be answered by viewing the audits.
This led to explaining how to use audits. In SQL Server 2008 (and 2008 R2), hereinafter referenced as SQL 2008, auditing is so easy, there is no reason not to do it. Basic process for creating an audit is to create audit and then define the specification for the audit.
In simple terms, the audit is where the output from the specification will be stored. The audit can be one of three things as of the time of this writing: a file on the file system, the application event log in Windows, or the security event log in Windows (not available in Windows XP). If choosing the security event log, Microsoft states “the account that the SQL Server service is running under must have the generate security audits permission to write to the Windows Security log.”
Now that an audit is setup, the specification can be set. This will define the information that you would like to capture, like when a server principal changes. When defining the specification, you have to choose an audit. Together, you will capture the information you are looking to monitor. One thing to keep in mind is the scope of the audit: It can be server level or database level.
In this particular example, I created a total of six audits and six audit specifications. The purpose of the six was to track anything that happens with user accounts. The six specifications I used were:
- DATABASE_PERMISSION_CHANGE_GROUP
- SERVER_PERMISSION_CHANGE_GROUP
- DATABASE_ROLE_MEMBER_CHANGE_GROUP
- SERVER_ROLE_MEMBER_CHANGE_GROUP
- DATABASE_PRINCIPAL_CHANGE_GROUP
- SERVER_PRINCIPAL_CHANGE_GROUP
These particular six were chosen because they track different things. Microsoft has a complete list describing these groups are here, (at least until the link dies). With the six, anytime a user is modified, it can be found. Examples are create/drop/modify server login, modify server login role, create/drop/modify databae user, modify database role, grant/revoke/deny server login permission, and grant/revoke/deny database user permissions.
When creating audits and audit specifications, they are disabled by default. Don’t forget to enable the audits. If forgotten, the audit (remember above the audit is where the output is going) will have no information about the specification you defined.