Need to know when was the account granted access and who granted that

Question

Need to know when was the account granted access and who granted that

Feivel

SSCertifiable

Points: 7524
More actions
February 22, 2018 at 2:15 pm

#410262

All,
I would like to know if there is a way I can find out when a specific user was granted access rights to some DBs and when it was granted. For e.g we have a user called xx\yy and this user has read rights on let's say A and B DBs but we didn't wanted this user to have this level access, so basically I would like to know who was the one who granted the access rights and when it was granted...basically getting to know the chain of events.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

SQLPirate SSCrazy Points: 2880 More actions · Answer 1

You can check the default trace if it happened recently enough. This query (stolen from RedGate) will work.
SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , t.RoleName , t.TargetUserName , t.TargetLoginName , t.SessionLoginName FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event', 'Audit Add Member to DB Role Event' ) AND v.subclass_name IN ( 'add', 'Grant database access' )

If it's not there you can try looking at previous .trc files as this only looks at the current one. If you check the results of the "select top 1" subquery you'll get a result like: C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log\log_124.trc. Use that result to replace the subquery with the previous file (log_123.trc in this case) and work your way back as long as you've got files.

SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , t.RoleName , t.TargetUserName , t.TargetLoginName , t.SessionLoginName FROM sys.fn_trace_gettable('C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log\log_123.trc', DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event', 'Audit Add Member to DB Role Event' ) AND v.subclass_name IN ( 'add', 'Grant database access' )