Sql-server – SQL Server 2008 R2 – user disk quota

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

Error: 18456, Severity: 14, State: 8.

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid 
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required

The "Security Audit>Audit Add Login to Server Role Event" will capture role drops as well, not just role adds. However, that one alone might not give you the information you're looking for, depending on what you need (such as the specific statement executed if necessary). So you could also add the Stored Procedure>RPC: Completed event, and if you want to get really granular add the SP: StmtCompleted as well.

An alternative could also be to use a server-level DDL trigger. I use a trigger on the ADD_SERVER_ROLE_MEMBER event so I can catch if one of my admins decides to throw someone into the sysadmin role without my knowledge. In my case I have an admin database with a table designed to store DDL event info and I insert a record for this type of event, among others. The full list of events you can create a trigger on is here: http://msdn.microsoft.com/en-us/library/bb522542.aspx and there are good basic examples of how to use these events in T-SQL in the trigger body here: http://msdn.microsoft.com/en-us/library/ms175941.aspx