TSQL to Get all details of who created the SQL Server Login on a SQL server Instance

As a DBA, One of the most important task is to audit the security.

Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.

So here a small simple query to all such details.

Firstly we need to get the path of default Trace which is running on SQL Server Instance,
The below query will assist you in getting that path.

select value from ::fn_trace_getinfo(0)

Now you will get values something like below. The Path may vary how you have configured it to be and it will show you the default trace which are currently running.

Now copy the path which you have found out in above query and replace "PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere" with the path which you have found in the below query to get the required details.

SELECT  ste.name
        ,sftgt.DatabaseName 
        ,sftgt.NTDomainName 
        ,sftgt.ApplicationName 
        ,sftgt.LoginName 
        ,sftgt.StartTime 
        ,sftgt.TargetLoginName 
        ,sftgt.SessionLoginName
FROM    sys.fn_trace_gettable('PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere', DEFAULT) sftgt
        JOIN sys.trace_events ste ON sftgt.EventClass = ste.trace_event_id
        JOIN sys.trace_subclass_values stsv ON stsv.trace_event_id = ste.trace_event_id
                                            AND stsv.subclass_value = sftgt.EventSubClass
WHERE   ste.name = 'Audit Addlogin Event'
        AND stsv.subclass_name = 'add'
GO

You can customize this query.
Note: This feature is available from SQL Server 2005 onwards only.

You get many more information from default trace. Will cover more in further posts.