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.
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:
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.
answered Dec 11, 2012 at 02:49 PM
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
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL \LOG\log_4192.trc',0)
ON e.category_id = cat.category_id
where e.name='Audit Add Login to Server Role Event'
order by starttime desc
answered Dec 14, 2012 at 08:05 AM