Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What program are you uninstalling? Does that user have anything relation to the program you are uninstalling? –  SQLRockstar Aug 22 '12 at 2:45
add comment

migrated from stackoverflow.com Aug 21 '12 at 23:14

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
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? –  StickFigs Aug 22 '12 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) –  sqlbattsman Aug 22 '12 at 17:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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