4

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?

1
  • What program are you uninstalling? Does that user have anything relation to the program you are uninstalling? Commented Aug 22, 2012 at 2:45

1 Answer 1

3

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

2
  • Thank you! I think I found where it happens, it's a SP: StmtCompleted event that reads sp_dropsrvrolemember [NPTAD\vmuser], [sysadmin]. Does this mean that somewhere this exact line of code is being executed or is this an output of the results of executing code?
    – Kyle V.
    Commented Aug 22, 2012 at 13:48
  • 1
    I'm assuming that the "vmuser" account is registered with this software application in some way, such as a service account, or DB access account? If so, it would appear that one function of the uninstall process for this software is to remove sysadmin permissions from the service account(s) used. Yes, that is the SQL statement that would be executing, which is a call to a system stored procedure "sp_dropsrvrolemember" passing the parameters of [NPTAD\vmuser] (the user to execute against) and [sysadmin] (the role the user is to be removed from) Commented Aug 22, 2012 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.