I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to happen? If so which events do I need to check off on the Events Selection screen?
Sql-server – Microsoft SQL Server 2008 – Catching user role changes with SQL Server Profiler
profilersql-server-2008
Related Question
- Sql-server – Error using triggers with SQL Server Profiler Tables
- Sql-server – SQL Profiler Shows Update (EventClass) with No Errors, But Data in Table is Not Changed
- Sql-server – How to trace steps in SQL profiler for only particular application
- Sql-server – SQL Server 2008 service keeps crashing
- Sql-server – What interval does SQL Server Profiler’s “duration” refer to
Best Answer
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