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] After: ![alt text] : /storage/temp/523-login+before.jpg : /storage/temp/524-login+after.jpg
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