DBA Audit

Building on my “is the database used?” article I developed this SQL-job as a light-weight audit to find unused databases and logins.

The job was scheduled to run every minute on every server, and had to work on SQL 2000, 2005, 2008r2 and 2012.

There were 4 steps

1) Initial Setup – Database Audit

--create table of database names

	select [name] dbase_name, [dbid], cast(null as datetime) last_used
	into [master].[dbo].[dbaudit]
	from [master].[dbo].[sysdatabases]
	where [dbid] > 4

2) Initial Setup – Login Audit

-- initial setup

	CREATE TABLE loginAudit (
		login_name VARCHAR(200), last_login_date DATETIME)

	INSERT INTO loginAudit (login_name, last_login_date)
		SELECT DISTINCT sl.name, NULL FROM syslogins sl

Steps 1 and 2 create the tables to hold the results, and interestingly carry on to the next step if they fail (IE: the tables already existed). I did it this way so I could just run it without any preparation.

3) Update Results – Database Audit

--update connections

	update [master].[dbo].[dbaudit]
	set last_used = getdate()
	where [dbid] in 
		(select [dbid] from [master].[dbo].[sysprocesses])

4) Update Results – Login Audit

-- update logins
		
	SELECT MAX(login_time) [login_time], loginame login_name INTO #loginTempTable
	FROM master.dbo.sysprocesses
	where loginame not in ('sa')
	and loginame not like ' %'
	GROUP BY loginame

	UPDATE loginAudit
	SET last_login_date = ltt.login_time 
	FROM #loginTempTable ltt
	WHERE loginAudit.login_name = ltt.login_name

I wanted steps 3 and 4 to capture the current information without any table growth, so if my little job ran for years unnoticed the results-tables would not be huge.

BTW, I developed this on a candidate server from each of the 4 SQL versions, before scripting out the job on SQL server 2012 (“Drop and Create”).

FYI, I tested the resulting script (“CreateJob_DBAAudit.sql”) on the 4 servers again, and made some minor adjustments (so it would work more than once, and without errors on SQL-2000), before deploying to all 300+ production servers via SSMS Registered Servers.