This post has been wikified, any user with enough reputation can edit it.
asked
is it possible to track who removed the sysadmin role to particular user?
hi I am using SQL SERVER 2005,We have 5 sysadmin role users are working in MY organization include me. I have a userid login credentials sysadmin role privileges in SQL Server. But My problem is **every week some one removed the Sysadmin privileges from My userid login. then I asked my management to get back the sysadmin privileges in my login.** Repeatedly, Every Week I faced this problem someone removed MY Sysadmin privileges in SQL Server and I asked for management to get back. So **1) Is it possible for Track or See which user is removed sysadmin privileges on My login????** **2)what user and what type of privileges he removed corresponding to that user?** see the figure for Before and After: Before: ![alt text][1] After: ![alt text][2] [1]: /storage/temp/523-login+before.jpg [2]: /storage/temp/524-login+after.jpg
You can use the server's default trace to try to pinpoint who is removing the access. This is a security event that should be captured, assuming that the default trace hasn't been modified.
http://blogs.technet.com/b/beatrice/archive/2008/04/29/sql-server-default-trace.aspx Hope this helps!
If you had some sort of custom auditing then yes, but if not the default trace would be the only way. The default trace only stores a number of the latest actions taken, so if the server is very busy you will need to be quick to find anything out.
I just realized that I gave a link for 2008 when the OP is on 2005. You can still read the roll over files in 2005 by specifying the number of files.
http://msdn.microsoft.com/en-us/library/ms188425(v=sql.90).aspx My apologies for any confusion. My fault.
The actions recorded in the default trace are not deleted. It roles over to a new file after it reaches a certain file size. If you don't find it as soon as it happens you will have to go through the previous files individually which can be very time consuming.
Hi John Morehouse & anthony Thanking you for your answer of my question no 1 it Answered my question half. But i want answer of question no 2. Because it gives the username login for who removed the privileges only but i want **which user login he removed the privileges and what privileges that user removed details.**? kindly see the figure. it gives the who removed the user name only and it shows objectname as null and textdata as null. Is any other method to Trace for what type privilege that user removed? which user login he removed? ![alt text][1] [1]: /storage/temp/525-after+default+trace.jpg ***is any one there to help me?????????????***
Thanks for everyone below two query has used to Track who has done ? what has done? thanks for every one.................especially **JohnM.** SELECT * FROM ::fn_trace_getinfo(default) kindly run the first query to get the C:/programmefile/...trc files and paste to second query and run the second query then u can get the details if u know which time it happens..... i think it may help .......check it SELECT top 15 loginname, textdata, RoleName, TargetLoginName, starttime, e.name as EventName, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, duration, eventclass, eventsubclass, loginsid, endtime, spid FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL \LOG\log_4192.trc',0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id where e.name='Audit Add Login to Server Role Event' order by starttime desc
12 People are following this question.
