x

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

login before.jpg (39.7 kB)
login after.jpg (39.5 kB)
more ▼

asked Dec 11, 2012 at 12:52 PM in Default

askmlx121 gravatar image

askmlx121
2.5k 67 74 77

if any one help me i will give vote 5
Dec 12, 2012 at 08:27 AM askmlx121
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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!
more ▼

answered Dec 11, 2012 at 02:49 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

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.
Dec 12, 2012 at 10:34 AM anthony.green

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

is any one there to help me?????????????
Dec 13, 2012 at 09:39 AM askmlx121
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.
Dec 13, 2012 at 01:34 PM JohnM
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.
Dec 13, 2012 at 01:42 PM Shawn_Melton

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.
Dec 13, 2012 at 02:13 PM JohnM
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 14, 2012 at 08:05 AM

askmlx121 gravatar image

askmlx121
2.5k 67 74 77

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.
Dec 14, 2012 at 01:11 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x163
x86

asked: Dec 11, 2012 at 12:52 PM

Seen: 1546 times

Last Updated: Dec 14, 2012 at 01:11 PM