Logins Audit

This script allows you to audit logins on your SQL Servers. It produces three web pages which are emailed to you:

Requirements:

Download/Script:

Logins_Audit.zip

------------------------------------------------------------------------------
-- Logins_Audit.sql
-- Created by John Buoro (http://www.VirtualObjectives.com.au)
--
-- Used to audit the level of access on SQL Server. Loops through all databases 
-- and obtains members for database roles as well as server role members.
-- It creates three html reports which are then emailed.
--
-- spRoleMembers was created by Randy Dyess www.TransactSQL.Com
--
-- Requires xp_smtp_sendmail. See http://www.sqldev.net/xp/xpsmtp.htm
-- Please ensure that you change the Configuration variables section below.
------------------------------------------------------------------------------

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.spRoleMembers') IS NOT NULL
DROP PROCEDURE spRoleMembers
GO

CREATE PROCEDURE spRoleMembers
AS

SET NOCOUNT ON

--Variables.
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

--Temp table to hold database and user-define role user names.
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)

--Temp table to hold database names.
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)

--Create table.
IF OBJECT_ID ('tempdb.dbo.tRolemember') IS NULL
BEGIN
        CREATE TABLE tempdb.dbo.tRolemember
        (
        strServerName VARCHAR(50)
        ,strDBName VARCHAR(50)
        ,strRoleName VARCHAR(50)
        ,strUserName VARCHAR(50)
        ,strUserID VARCHAR(100)
        )
END

--Obtain members of each server role.
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember

--Obtain database names.
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT

--Loop through databases to obtain members of database roles and user-defined roles.
WHILE @lngCounter > 0
BEGIN
        --Get database name from temp table.
        SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)

        --Obtain members of each database and user-defined role.
        SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
        EXEC ' + @strDBName + '.dbo.sp_helprolemember'

        EXEC sp_executesql @strSQL

        --Update database name in temp table
        UPDATE #tRolemember
        SET strDBName = @strDBName
        WHERE strDBName IS NULL

        SET @lngCounter = @lngCounter - 1
END

--Place data into table.
INSERT INTO tempdb.dbo.tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN tempdb.dbo.tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL

GO

------------------------------------------------------------------------------
-- Configuration variables.

DECLARE @MailAlertTo varchar(100)
DECLARE @SMTPServer varchar(30)
DECLARE @ReportFiles varchar(1000)

SET @MailAlertTo = 'admin@domainname.com.au' -- Email address to send the reports to.
SET @SMTPServer = 'SMTPSERVER' -- SMTP server name.

-- Generate reports.

-- Get Role Members.
EXEC dbo.spRoleMembers

-- Get the %TEMP% environment variable.
DECLARE @tempvar varchar(1000)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempvar = (SELECT top 1 info FROM #tempvartable)
DROP TABLE #tempvartable

-- Generate HTML results for query.
DECLARE @t varchar(1000)
DECLARE @s varchar(1000)
SET @ReportFiles = ''

SET @t = @@servername + ' Access by User'
SET @s = @tempvar + '\' + @t + '.htm'
SET @ReportFiles = @ReportFiles + @s + ';'
EXEC master..sp_makewebtask 
        @outputfile = @s, 
        @query = 'SELECT strUserName as [User Name], 
		strDBName as [Database Name], 
		strRoleName as [Role Name], 
		strServerName as [Server Name] 
		FROM tempdb.dbo.tRolemember 
		WHERE strUserName <> ''dbo'' ORDER BY strUserName',
        @lastupdated = 1,
        @HTMLheader = 3,
        @webpagetitle = @t,
        @resultstitle = @t

SET @t = @@servername + ' Access by Role'
SET @s = @tempvar + '\' + @t + '.htm'
SET @ReportFiles = @ReportFiles + @s + ';'
EXEC master..sp_makewebtask 
        @outputfile = @s, 
        @query = 'SELECT strRoleName, 
		strDBName as [Database Name], 
		strUserName as [User Name], 
		strServerName as [Server Name] 
		FROM tempdb.dbo.tRolemember 
		WHERE strUserName <> ''dbo'' ORDER BY strRoleName',
        @lastupdated = 1,
        @HTMLheader = 3,
        @webpagetitle = @t,
        @resultstitle = @t

SET @t = @@servername + ' Access by Database'
SET @s = @tempvar + '\' + @t + '.htm'
SET @ReportFiles = @ReportFiles + @s + ';'
EXEC master..sp_makewebtask 
        @outputfile = @s, 
        @query = 'SELECT strDBName, 
		strRoleName as [Role Name], 
		strUserName as [User Name], 
		strServerName as [Server Name] 
		FROM tempdb.dbo.tRolemember 
		WHERE strUserName <> ''dbo'' ORDER BY strDBName',
        @lastupdated = 1,
        @HTMLheader = 3,
        @webpagetitle = @t,
        @resultstitle = @t

-- Email the reports.

DECLARE @dom varchar(100)
DECLARE @svr varchar(100)
DECLARE @sub varchar(100)
DECLARE @rc int

SET @dom = SUBSTRING(@MailAlertTo, CHARINDEX('@',@MailAlertTo)+1, 100)
SET @svr = (select @@servername) + @dom
SET @sub = 'Logins Audit of SQL Server ' + @@servername

-- xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm
EXEC @rc = master.dbo.xp_smtp_sendmail
        @from = @svr,
        @from_name = @@servername,
        @to = @MailAlertTo,
        @priority = 'HIGH',
        @subject = @sub,
        @type = 'text/html',
        @attachments = @ReportFiles,
        @server = @SMTPServer

-- Clean up.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.spRoleMembers') IS NOT NULL 
        DROP PROCEDURE spRoleMembers

IF OBJECT_ID('tempdb.dbo.tRolemember') IS NOT NULL
        DROP TABLE tRolemember

DECLARE @tmppath varchar(1000)
SET @tmppath = 'del /Q "%TEMP%\' + @@servername + ' Access by *.htm"'
EXEC xp_cmdshell @tmppath

------------------------------------------------------------------------------

Acknowledgments: