SQL Saturday Atanta

I had a great time in Atlanta at SQL Saturday and think that the audience enjoyed it as well.   I have received some emails that people are having trouble downloading my presentation.  I tried to down the PowerPoint from the SQL Saturday site as well and had some difficulty.  Here is a link to my presentation for those that have trouble.

Audit for New Login without using SQL Audit

Because SQL Audit is only an Enterprise Edition feature, I have been thinking about the best to audit for the creation of a login without it.  I do not need to know real time when a login is created, a daily report will suffice.  This is not as simple as you may think.   I am not going to give you the whole script, but you should be able to work it out for yourself from here.

First lets create a login

USE [master]

GO

CREATE LOGIN [Test_Login] WITH PASSWORD=N'TestLoginPASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

A query of syslogins will tell you that a new user has been created since the last time you checked

SELECT createdate, name, dbname, isntuser

FROM sys.syslogins

WHERE createdate > @LastDate

ORDER BY createdate DESC

However,  this information does not tell me what I really want to know.   Who created this login?  Since I do not want to write an elaborate thing to capture this information, I want to query an exisiting source: The defeault trace.  

This comes with a problem of its own.  The default trace rolls over and only 5 are kept.  The limit of 5 is not so much of a problem in my world, but not knowing when the rollover happens is.   You can query the ::fn_trace_getinfo to get metadata about these traces, but we need to know which trace contains the information we need. 

SELECT * from ::fn_trace_getinfo(0) Where traceid = 1

This returns the following

traceid	property	value
1	1	2
1	2	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc
1	3	20
1	4	NULL
1	5	1

The property values are

1= Trace options.
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status.  0 = stopped. 1 = running.

The stop time would help us, but I have yet to run this query on a server or a log and not get a null result.  In order to find out if the trace contains the data we are looking for we must query the file.  You can use the meta data collected in the last query to get the file path.

SELECT MAX(StartTime) as EndTIme , MIN(startTime) as StartTime

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

This will let us know if our create date from syslogins in within this trace file.  If it is we can then query the trace to get infromation on the person who created the login.  Event class 104 is the event for the creation of a new login. 

SELECT StartTime, LoginName, Hostname, TargetLoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

WHERE EventClass = 104

Another approach is to query the default trace only, you will still have to do some date management to make sure you don’t miss any new logins between trace files.