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?
migrated from stackoverflow.com Aug 21 '12 at 23:14
This question came from our site for professional and enthusiast programmers.
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