Sql-server – find out in SQL SERVER 2012, who is creating SQL Logins and on which date

auditloginssql serversql-server-2012

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

Best Answer

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

Related Question