SQLServer SQL Server Login and Permission Audit Report Using Database Mail

SQL Server Login and Permission Audit Report Using Database Mail

June 9, 2026June 9, 2026|

SQL Server Login & Permission Audit Report Using Database Mail

Overview

Managing and reviewing user access is a critical part of database administration and information security. Organizations regularly perform Internal Audit (IA), User Access Review (UAR), and compliance assessments to ensure that users have only the permissions required to perform their duties.

This SQL Server script automates the collection of login information, role memberships, and database permissions across SQL Server databases and delivers the results in a professionally formatted HTML email using Database Mail.

Key Features

Feature Description
Fixed Server Role Audit Captures server-level role memberships such as sysadmin, securityadmin, dbcreator, serveradmin, and bulkadmin.
Fixed Database Role Audit Collects database role memberships such as db_owner, db_datareader, db_datawriter, and db_securityadmin.
Database Permission Audit Reports explicit permissions such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, and CONTROL.

 

Fixed Server Role Audit

Information Captured
Login Name
Server Role Name
Login Type
Login Status (Enabled/Disabled)

 

Fixed Database Role Audit

Information Captured
Database Name
Database Role
Role Member
Principal Type

 

Database Level Permission Audit

Permissions Captured
SELECT
INSERT
UPDATE
DELETE
EXECUTE
ALTER
CONTROL

 

Professional HTML Report

Report Component Purpose
Application Name Identifies the application being audited
Database Name Identifies the target database
SQL Instance Name Identifies the SQL Server instance
Report Date Shows when the report was generated
Prepared By Indicates report ownership

 

Benefits

Benefit Description
Audit Readiness Reduces manual effort during audits.
Security Visibility Provides complete visibility into SQL Server access.
Automation Eliminates manual collection and reporting activities.
Compliance Support Supports ISO 27001, PCI DSS, SOX, and internal audits.

 

Prerequisites

Requirement
Database Mail must be configured.
SQL Server Agent should be running for scheduling.
Appropriate permissions must exist.
Database Mail profile must be operational.

 

Script

SQL Script
/*======================================================
SQL Server Login & Permission Audit Report
=======================================================*/
IF EXISTS (SELECT *
FROM tempdb.sys.all_objects
WHERE name LIKE ‘%#Login_Audit%’)
DROP TABLE #Login_Audit;CREATE TABLE #Login_Audit
(
A NVARCHAR(500),
B NVARCHAR(500) DEFAULT(”),
C NVARCHAR(200) DEFAULT(”),
D NVARCHAR(200) DEFAULT(”)
);/*=====================================================
FIXED SERVER ROLES
=======================================================*/
INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘– FIXED SERVER ROLE DETAILS –‘,’—–‘,’—–‘,’—–‘;
INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘LOGINS’,’ROLE Name’,’Type’,’Login Status’;
INSERT INTO #Login_Audit (A,B,C,D)
SELECT
a.name,
c.name,
a.type_desc,
CASE a.is_disabled
WHEN 1 THEN ‘Disabled’
ELSE ‘Enabled’
END
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.member_principal_id
INNER JOIN sys.server_principals c
ON c.principal_id = b.role_principal_id
WHERE a.name NOT LIKE ‘NT AUTHORITY%’
AND a.name NOT LIKE ‘NT SERVICE%’
ORDER BY c.name;
/*==============================================
FIXED DATABASE ROLES
===============================================*/

INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘– FIXED DATABASE ROLES DETAILS –‘,’—–‘,’—–‘,’—–‘;

INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘Database Name’,’Role Name’,’Member’,’Type’;

INSERT INTO #Login_Audit
EXEC master.dbo.sp_MSforeachdb ‘
USE [?];SELECT
DB_NAME() AS DBNAME,
c.name AS DB_ROLE,
a.name AS ROLE_MEMBER,
a.type_desc
FROM sys.database_principals a
INNER JOIN sys.database_role_members b
ON a.principal_id = b.member_principal_id
INNER JOIN sys.database_principals c
ON c.principal_id = b.role_principal_id
WHERE DB_NAME() NOT IN (”master”,”model”,”msdb”,”tempdb”)
AND a.name <> ”dbo”
AND a.name NOT LIKE ”BUILTIN%”
AND a.name NOT LIKE ”NT AUTHORITY%”
AND c.is_fixed_role = 1’;

/*========================================================
DATABASE LEVEL PERMISSIONS
========================================================*/

INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘– DATABASE LEVEL PERMISSION DETAILS –‘,’—–‘,’—–‘,’—–‘;

INSERT INTO #Login_Audit (A,B,C,D)
SELECT ‘Database Name’,’Login Name’,’Permission’,’Status’;

INSERT INTO #Login_Audit
EXEC master.dbo.sp_MSforeachdb ‘
USE [?];

SELECT
DB_NAME() AS DBNAME,
b.name AS USERNAME,
a.permission_name,
a.state_desc
FROM sys.database_permissions a
INNER JOIN sys.database_principals b
ON a.grantee_principal_id = b.principal_id
WHERE DB_NAME() NOT IN (”master”,”model”,”msdb”,”tempdb”)
AND a.class = 0
AND b.name <> ”dbo”
AND b.name <> ”guest”
AND b.name NOT LIKE ”%#%”
AND b.name NOT LIKE ”NT AUTHORITY\SYSTEM”
AND b.name NOT LIKE ”NT AUTHORITY\NETWORK SERVICE”
AND b.name NOT LIKE ”MS_DataCollectorInternalUser”
AND b.name NOT LIKE ”BUILTIN\Administrators”
AND a.permission_name <> ”CONNECT”’;

/*=====================================================
BUILD HTML REPORT
======================================================*/

DECLARE @HTML NVARCHAR(MAX);
DECLARE @ReportDate VARCHAR(50);
SET @ReportDate = FORMAT(GETDATE(),’dd-MMMM-yyyy h:mmtt’);

SET @HTML = N’

<html>
<head>
<style>

body
{
font-family: Segoe UI, Calibri, Arial;
font-size: 10pt;
}

table
{
border-collapse: collapse;
width: 100%;
}

td
{
border: 1px solid #000000;
padding: 6px;
}

.section
{
background-color: #1F4E78;
color: white;
font-weight: bold;
text-align: center;
font-size: 12pt;
}

</style>
</head>

<body>

<h2 style=”color:#1F4E78;”>
SQL Server Login & Permission Audit Report
</h2>

<table style=”width:60%;margin-bottom:15px;”>

<tr>
<td style=”background-color:#1F4E78;color:white;font-weight:bold;width:30%;”>
Application Name
</td>
<td>’ + + ‘</td>
</tr>
<tr>
<td style=”background-color:#1F4E78;color:white;font-weight:bold;width:30%;”>
Database Name
</td>
<td>’ + + ‘</td>
</tr>

<tr>
<td style=”background-color:#1F4E78;color:white;font-weight:bold;width:30%;”>
SQL Instance Name
</td>
<td>’ + @@SERVERNAME + ‘</td>
</tr>

<tr>
<td style=”background-color:#1F4E78;color:white;font-weight:bold;”>
Report As On
</td>
<td>’ + @ReportDate + ‘</td>
</tr>

<tr>
<td style=”background-color:#1F4E78;color:white;font-weight:bold;”>
Report Prepared By
</td>
<td>DBA Team</td>
</tr>

</table>

<br>

<table>’;

SELECT @HTML = @HTML +
CASE

WHEN A LIKE ‘– FIXED SERVER ROLE DETAILS%’ THEN
‘<tr class=”section”>
<td colspan=”4″>FIXED SERVER ROLE DETAILS</td>
</tr>’

WHEN A LIKE ‘– FIXED DATABASE ROLES DETAILS%’ THEN
‘<tr class=”section”>
<td colspan=”4″>FIXED DATABASE ROLES DETAILS</td>
</tr>’

WHEN A LIKE ‘– DATABASE LEVEL PERMISSION DETAILS%’ THEN
‘<tr class=”section”>
<td colspan=”4″>DATABASE LEVEL PERMISSION DETAILS</td>
</tr>’

/* Fixed Server Role Header */
WHEN A=’LOGINS’ THEN
‘<tr style=”background-color:#DCE6F1;font-weight:bold;text-align:center;”>
<td>’ + A + ‘</td>
<td>’ + B + ‘</td>
<td>’ + C + ‘</td>
<td>’ + D + ‘</td>
</tr>’

/* Fixed Database Role Header */
WHEN A=’Database Name’
AND B=’Role Name’ THEN
‘<tr style=”background-color:#D9EAD3;font-weight:bold;text-align:center;”>
<td>’ + A + ‘</td>
<td>’ + B + ‘</td>
<td>’ + C + ‘</td>
<td>’ + D + ‘</td>
</tr>’

/* Database Permission Header */
WHEN A=’Database Name’
AND B=’Login Name’ THEN
‘<tr style=”background-color:#FCE4D6;font-weight:bold;text-align:center;”>
<td>’ + A + ‘</td>
<td>’ + B + ‘</td>
<td>’ + C + ‘</td>
<td>’ + D + ‘</td>
</tr>’

ELSE
‘<tr>
<td>’ + ISNULL(A,”) + ‘</td>
<td>’ + ISNULL(B,”) + ‘</td>
<td>’ + ISNULL(C,”) + ‘</td>
<td>’ + ISNULL(D,”) + ‘</td>
</tr>’

END
FROM #Login_Audit;

SET @HTML = @HTML + ‘

</table>
<br><br>
<tr>
<td style=”
border-top:2px solid #1F4E78;
border-left:none;
border-right:none;
border-bottom:none;
padding-top:10px;”>

Regards,<br>
DBA Team<br>
Database Administration

</td>

</tr>
</table>

</body>
</html>’;

/*========================================================
SEND EMAIL
=========================================================*/

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBAActivity’,
@recipients = ‘dba@company.com’,
@copy_recipients = ‘audit@company.com;manager@company.com’,
@subject = ‘SQL Server Login & Permission Audit Report’,
@body = @HTML,
@body_format = ‘HTML’,
@importance = ‘HIGH’,
@sensitivity = ‘CONFIDENTIAL’;
Sample Mail
SQL Server audit report workflow diagram

 

Conclusion

This SQL Server Login & Permission Audit Report provides database administrators with an efficient method to review and monitor access across SQL Server environments.

By combining server role auditing, database role auditing, and database permission auditing into a single HTML report, organizations can improve security governance, streamline audit activities, and maintain compliance with internal and external requirements.

A well-designed audit report not only saves time but also helps ensure that database access remains secure, controlled, and fully documented.

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post