4

I was trying to find out in sql server 2012, who created login and which date. Can any body help on this thanks,

1 Answer 1

5

SQL server wont generally keep a track of this. So you have to make use of the default trace to gather the information for EventClass 109.

Make sure to check that default trace is running using:

SELECT* FROM sys.configurations WHERE configuration_id = 1568

If not enabled, then enable it using:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

Then you can run the query as mentioned here to gather the information for the added SQL logins:

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' )

Note * There are 5 default trace files and these are rolled over,hence its possible to capture recent information related to objects and not related to old events. Therefore to save the information for future use please read Collecting the Information in the Default Trace

3
  • Thanks for the quick response. I tried creating logins and deleted logins after trace enabled, but above query I ran is not capturing the logins details (added and deleted). I want to find out who created sql logins.
    – Bhupendra
    Mar 4, 2016 at 7:42
  • If you have SQL 2012 Ent Edtion, you can try configuring SQL audit feature.
    – SQLPRODDBA
    Mar 4, 2016 at 8:31
  • 1
    Audit is overkill. Isn't the problem just that you need a space between Add and login in the 3rd last line? Mar 4, 2016 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.