question

askmlx121 avatar image
askmlx121 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
sql-server-2005securitylogin
login before.jpg (38.7 KiB)
login after.jpg (38.6 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image askmlx121 commented ·
if any one help me i will give vote 5
0 Likes 0 ·
JohnM avatar image
JohnM answered
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!
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image anthony.green commented ·
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.
2 Likes 2 ·
JohnM avatar image JohnM commented ·
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.
2 Likes 2 ·
JohnM avatar image JohnM commented ·
Look for the RoleName & TargetLoginName columns in that return. It should show which role was applied (or removed) and which target login it was applied against.
1 Like 1 ·
Shawn_Melton avatar image Shawn_Melton commented ·
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.
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
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?????????????***
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
hi Johnm thanks a lot ..**RoleName & TargetLoginName columns.**....yes it helped me to find out for which user he removed thanking you thanking you.............................................................................
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Correct in that the default trace will roll over to new files. You can, however use the "fn_trace_gettable" function to read the roll over files in conjunction so that you don't have to read them individually. For SQL 2008: http://msdn.microsoft.com/en-us/library/ms188425(v=sql.105).aspx Take a look at the remark section. Just an FYI. ;-)
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
hi thanking you johnM **RoleName & TargetLoginName** it helped to show the which user and what type of privilegs he removed thanking you...........all
0 Likes 0 ·
askmlx121 avatar image
askmlx121 answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Glad it worked out for you! Please make sure to mark one of the answers as accepted so that others know you found an answer. This will also help future users who might have the same question.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.