AWS RDS DBA Tasks SQL Server

SQL Server Audit Daily Email

By David Speight / April 19, 2023

Not so long ago my company acquired another subsidiary. Each morning I’ve been receiving an AUDT REPORT that I get from each database instance. However, some of my subsidiary companies have servers with the same name just at a different location. To solve my confusion each morning regarding which location I was looking at, given the coffee is still brewing, I rewrote the email report to get a better handle on this. In the future, I plan to create a solution that just sends one email for all locations, but for now, I’ll update the one I have and add a location tag.

BACKGROUND: This article assumes I am familiar with SQL AUDITING and have implemented a solution similar to that described by K. Brian Kelley and Jason Brimhall in these articles:

https://www.mssqltips.com/sqlservertip/2741/how-to-audit-login-changes-on-a-sql-server/

https://jasonbrimhall.info/2015/03/11/audit-schema-change-report/

I use both of these sources and have my own revision of their combined code that I hope to post in the future. Check out my GIT repository later.

https://github.com/LoveTheSql

Back to my morning email. It is produced by a stored procedure and sends the data via sql mail. My stored proc has five sections. Login counts, Login failures, Login Changes, Schema changes, Permission Changes.

Each section of the procedure queries data in my AUDIT database and pulls in stats for the previous day. My tables in the AUDIT database look like this:

Finally, it wraps it all up in a simple HTML wrapper and spits it out into an email that looks something like this screenshot:

I use Amazon RDS servers in Multi-Availability Zones for a lot of things, and thus “SELECT @@SERVERNAME” produces only the name of the active node and not my cluster/server name that I want. To get around this, I use a server table that I join on.

SELECT 
	UPPER(ISNULL(S.ServerResolvedName,L.ServerName)) AS ServerName, 
	ISNULL(L.Database,'') AS Database, 
	ISNULL(L.User,'') AS User, 
	...
FROM dbo.UserPermissionScan AS L
LEFT JOIN dbo.Server AS S ON L.ServerName = S.ServerNm
WHERE ...

Details on how to create this server table are found in my post, “SELECT @@SERVERNAME on AWS RDS.”

SELECT @@SERVERNAME on AWS RDS

Here is an overview of the email stored procedure I am revising:

  • Create an HTML style sheet for the email.
  • Create the HEADER.
  • Create five (5) sections for the BODY with tables and data.
  • Wrap it all together.
  • Send the email

/****** Object:  StoredProcedure [dbo].[E_Report_AuditDaily]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20201104
-- Revised: 20230419
-- =============================================
ALTER PROCEDURE [dbo].[E_Report_AuditDaily] 
@E_profile_name nvarchar(250),
@E_recipients nvarchar(2000),
@LocationName nvarchar(150), -- My new location tag.
@ServerName NVARCHAR(2000)=NULL,
@StartDate DATETIME=NULL,
@EndDate DATETIME=NULL
AS
BEGIN
-- This will email the Daily Audit Report
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @body1 nvarchar(max);
declare @body2 nvarchar(max);
declare @body3 nvarchar(max);
declare @body4 nvarchar(max);
declare @body5 nvarchar(max);
declare @bodyFIN nvarchar(max);
DECLARE @StyleSheet VARCHAR(1000);
DECLARE @Header VARCHAR(400);
DECLARE @GreenTitle VARCHAR(250);
DECLARE @ServerResolvedName VARCHAR(250);

SELECT TOP(1) @ServerResolvedName = ServerResolvedName
FROM dbo.Server
WHERE IsActive = 1;

SELECT 
@StyleSheet = 
	'<style><!--
	@font-face
	{font-family:"Microsoft Sans Serif";
	panose-1:2 11 6 4 2 2 2 2 2 4;}
	p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}
	--></style>',
@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:18pt;color:#8B404B;letter-spacing:-1.0pt">MyCompany</span><span style="font-size:18pt;color:#0C2340">report</span></p><BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">DAILY AUDIT LOG FOR ',UPPER(@LocationName),':',UPPER(@ServerResolvedName),' ON ',CONVERT(varchar(12),getdate()),' </span></p>'),
@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#8B404B;letter-spacing:-1.0pt">',
@StartDate = (CASE WHEN @StartDate IS NULL THEN CONVERT(Date,DATEADD(d,-1,getdate())) ELSE @StartDate END),
@EndDate = (CASE WHEN @StartDate IS NULL THEN CONVERT(Date,DATEADD(d,0,getdate())) ELSE @EndDate END);

-- LOGIN COUNTS
SELECT @body1 = cast( (
SELECT td = CONCAT(d.ServerInstanceName,'</td><td>',d.PrincipalName,'</td><td>',CONVERT(varchar(50),d.dDate),'</td><td>',(Case d.Success when 1 then 'Yes' Else 'No' END),'</td><td>',convert(varchar(50),d.Total) )
FROM (
	SELECT  
	UPPER(ISNULL(S.ServerResolvedName,L.ServerInstanceName))  AS ServerInstanceName, 
	ISNULL(PrincipalName,'') AS PrincipalName, 
	@EndDate AS dDate, 
	0 AS Success, 
	COUNT(*) AS Total
	FROM .[dbo].[LoginFailure] AS L
	LEFT JOIN dbo.Server AS S ON L.ServerInstanceName = S.ServerNm
	WHERE	(@ServerName IS NULL OR ServerInstanceName IN (SELECT [value] FROM dbo.SplitString(@ServerName,',')))
	AND (dtDate BETWEEN CONVERT(DATE,@StartDate) AND CONVERT(DATE,@EndDate)) 
	GROUP BY ISNULL(S.ServerResolvedName,L.ServerInstanceName), PrincipalName	
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
SELECT @body1 = (CASE WHEN len(@body1) IS NULL THEN '' ELSE		
		(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#DBCFD0">Instance</th><th style="background:#DBCFD0">Name</th><th style="background:#DBCFD0">Date</th><th style="background:#DBCFD0">Success</th><th style="background:#DBCFD0">Total</th></tr>'
	, replace( replace( @body1, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
SELECT @body1 = LEFT(@body1,40000) -- Limit to 40 of the 64k

----- LOGIN FAILURES
SELECT @body2 = cast( (
select td = CONCAT(d.ServerInstanceName,'</td><td>',d.PrincipalName,'</td><td>',CONVERT(varchar(50),d.dDate),'</td><td>',convert(varchar(500),d.StatementIssued) )
from (
	SELECT 
	UPPER(ISNULL(S.ServerResolvedName,L.ServerInstanceName)) AS ServerInstanceName, 
	ISNULL(PrincipalName,'') AS PrincipalName, 
	LEFT(dtDate,20) AS dDate, 
	ISNULL(StatementIssued,'') AS StatementIssued
	FROM dbo.LoginFailure AS L
	LEFT JOIN dbo.Server AS S ON L.ServerInstanceName = S.ServerNm
	WHERE	(@ServerName IS NULL OR ServerInstanceName IN (SELECT [value] FROM dbo.SplitString(@ServerName,',')))
	AND (dtDate BETWEEN @StartDate AND @EndDate) 
	AND (PrincipalName = '0' AND LEFT(StatementIssued,25) != 'Network error code 0x2746')
	GROUP BY ISNULL(S.ServerResolvedName,L.ServerInstanceName), PrincipalName, LEFT(dtDate,20), StatementIssued
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
SELECT @body2 = (CASE WHEN len(@body2) IS NULL THEN '' ELSE		
		(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#DBCFD0">Instance</th><th style="background:#DBCFD0">Name</th><th style="background:#DBCFD0">Date</th><th style="background:#DBCFD0">Statement Issued</th></tr>'
	, replace( replace( @body2, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
SELECT @body2 = LEFT(@body2,40000) -- Limit to 40 of the 64k

----- Login Changes Get
SELECT @body3 =	cast( (
select td = CONCAT(d.ServerInstanceName,'</td><td>',d.Name,'</td><td>',CONVERT(varchar(50),d.Type),'</td><td>',CONVERT(varchar(50),d.create_date),'</td><td>',CONVERT(varchar(50),d.modify_date),'</td><td>',CONVERT(varchar(50),d.AuditDtBegin),'</td><td>',CONVERT(varchar(50),d.AuditDtEnd),'</td><td>',convert(varchar(50),d.AuditAction) )
from  (
	SELECT 
	UPPER(ISNULL(S.ServerResolvedName,L.ServerInstanceName))  AS ServerInstanceName, 
	ISNULL([name],'') AS [name], 
	( CASE [type]	WHEN 'S' THEN 'Sql Login'
			WHEN 'U' THEN 'Windows Login'
			WHEN 'G' THEN 'Windows Group'
			WHEN 'R' THEN 'Server Role'
			WHEN 'C' THEN 'Certificate'
			WHEN 'K' THEN 'Asymmestric Key'
			ELSE 'Unknown' END 	) AS [Type], 
	create_date, modify_date, AuditDtBegin, AuditDtEnd,
	ISNULL(AuditAction,'') AS AuditAction
	FROM dbo.LoginScanRecord AS L
		LEFT JOIN dbo.Server AS S ON L.ServerInstanceName = S.ServerNm
	WHERE	(@ServerName IS NULL OR ServerInstanceName IN (SELECT [value] FROM dbo.SplitString(@ServerName,',')))
	AND (AuditdtBegin BETWEEN @StartDate AND @EndDate OR AuditdtEnd BETWEEN @StartDate AND @EndDate) 
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
SELECT @body3 = (CASE WHEN len(@body3) IS NULL THEN '' ELSE		
		(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#DBCFD0">Instance</th><th style="background:#DBCFD0">Name</th><th style="background:#DBCFD0">Type</th><th style="background:#DBCFD0">Create Date</th><th style="background:#DBCFD0">Modify Date</th><th style="background:#DBCFD0">Audit Date Begin</th><th style="background:#DBCFD0">Audit Date End</th><th style="background:#DBCFD0">Audit Action</th></tr>'
	, replace( replace( @body3, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
SELECT @body3 = LEFT(@body3,40000) -- Limit to 40 of the 64k

------- Schema Changes
SELECT @body4 =	cast( (
select td =  CONCAT(CONVERT(varchar(50),d.ChangeDate), '</td><td>', d.ServerName, '</td><td>', d.DatabaseName, '</td><td>', d.ObjectName, '</td><td>', d.ObjectType, '</td><td>', d.DDLOperation, '</td><td>', d.LoginName, '</td><td>', d.NTUserName, '</td><td>', d.ApplicationName)
from (
	SELECT 
	ChangeDate, 
	UPPER(ISNULL(S.ServerResolvedName,L.ServerName)) AS ServerName, 
	ISNULL(DatabaseName,'') AS DatabaseName, 
	ISNULL(ObjectName,'') AS ObjectName, 
	ISNULL(ObjectType,'') AS ObjectType, 
	ISNULL(DDLOperation,'') AS DDLOperation, 
	ISNULL(LoginName,'') AS LoginName, 
	ISNULL(NTUserName,'') AS NTUserName,
	ISNULL(ApplicationName,'') AS ApplicationName
	FROM dbo.SchemaChange AS L
	LEFT JOIN dbo.Server AS S ON L.ServerName = S.ServerNm
	WHERE	(@ServerName IS NULL OR ServerName IN (SELECT [value] FROM dbo.SplitString(@ServerName,',')))
	AND ChangeDate BETWEEN @StartDate AND @EndDate
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
SELECT @body4 = (CASE WHEN len(@body4) IS NULL THEN '' ELSE		
	(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#DBCFD0">Change Date</th><th style="background:#DBCFD0">Server Name</th><th style="background:#DBCFD0">Database</th><th style="background:#DBCFD0">Object Name</th><th style="background:#DBCFD0">Object Type</th><th style="background:#DBCFD0">DDL Operation</th><th style="background:#DBCFD0">LoginName</th><th style="background:#DBCFD0">NT User Name</th><th style="background:#DBCFD0">Application</th></tr>'
	, replace( replace( @body4, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
SELECT @body4 = LEFT(@body4,40000) -- Limit to 40 of the 64k

------- Permission Changes Get
SELECT @body5 =	cast( (
select td = CONCAT(d.ServerName,'</td><td>',d.[Database],'</td><td>',d.[User],'</td><td>',d.Permission,'</td><td>',d.Action,'</td><td>',d.Securable,'</td><td>',CONVERT(varchar(50),d.AuditDtBegin),'</td><td>',CONVERT(varchar(50),d.AuditDtEnd),'</td><td>',d.AuditAction)
from (
	SELECT 
	UPPER(ISNULL(S.ServerResolvedName,L.ServerName)) AS ServerName, 
	ISNULL([Database],'') AS [Database], 
	ISNULL([User],'') AS [User], 
	ISNULL(Permission,'') AS Permission, 
	ISNULL([Action],'') AS [Action], 
	ISNULL(Securable,'') AS Securable, 
	AuditDtBegin, 
	AuditDtEnd, 
	ISNULL(AuditAction,'') AS AuditAction
	FROM dbo.UserPermissionScan AS L
	LEFT JOIN dbo.Server AS S ON L.ServerName = S.ServerNm
	WHERE	(@ServerName IS NULL OR ServerName IN (SELECT [value] FROM dbo.SplitString(@ServerName,',')))
	AND (AuditDtBegin BETWEEN @StartDate AND @EndDate OR AuditDtEnd BETWEEN @StartDate AND @EndDate)
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
SELECT @body5 = (CASE WHEN len(@body5) IS NULL THEN '' ELSE		
		(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#DBCFD0">Instance</th><th style="background:#DBCFD0">Database</th><th style="background:#DBCFD0">User</th><th style="background:#DBCFD0">Permission</th><th style="background:#DBCFD0">Action</th><th style="background:#DBCFD0">Securable</th><th style="background:#DBCFD0">Begin</th><th style="background:#DBCFD0">End</th><th style="background:#DBCFD0">Audit Action</th></tr>'
	, replace( replace( @body5, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
SELECT @body5 = LEFT(@body5,40000) -- Limit to 40 of the 64k

SELECT @bodyFIN = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,'LOGIN FAILURES</span></p><BR />', @body1,'<BR /><BR />',@GreenTitle,'LOGIN COUNTS</span></p><BR />',@body2,'<BR /><BR />',@GreenTitle,'LOGIN CHANGES</span></p><BR />',@body3,'<BR /><BR />',@GreenTitle,'SCHEMA CHANGES</span></p><BR />',@body4,'<BR /><BR />',@GreenTitle,'PERMISSION CHANGES</span></p><BR />',@body5);
		
SELECT @bodyFIN = LEFT(@bodyFIN,60000); -- char limit 64k, leaving space for other vars

If LEN(@bodyFIN) > 10
BEGIN
DECLARE @SubjectTxt   VARCHAR(50);
SELECT @SubjectTxt   = CONCAT('Daily Audit ', UPPER(@LocationName),':', UPPER(@ServerResolvedName));
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @E_profile_name,
	@recipients = @E_recipients,
	@body = @bodyFIN,
	@subject = @SubjectTxt,
	@body_format = 'HTML'; 
END;

END
GO

You may also like