Who created a server login in SQL Server? Power of Default Trace.

Lets discuss this in a better and interesting way :

Business Manager  – Login ‘guyxyz’ is causing lot of issues and we need to know who created this login without our knowledge?

DBA : Hmm! I need some time to gather this info as this is not logged anywhere as per my understanding,and I would like to do an R&D to confirm that.

Our DBA in this case has to check the default trace(New feature starting 2005) which is logged by SQL server automatically to get a solution for this problem.

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.

The default trace will be located in the same directory where you placed your system databases and an example can be ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log ‘

We can use the below function to pull the information on logins :

SELECT TargetLoginName,SessionLoginName,NTUserName,NTDomainName,HostName,ApplicationName,StartTime

FROM fn_trace_gettable

(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_###.trc’, default)
where TargetLoginName=’loginname’


4 thoughts on “Who created a server login in SQL Server? Power of Default Trace.

  1. Pingback: Hidden gem – SP_CONFIGURE “A journey discussing each options” « smartsqlserver

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s