Tracking and reviewing user access in a SQL Server is an important tasks for any DBA. Unauthorized access or suspicious activity can comprise sensitive data, leading to business disruption, data breaches, or financial losses.

In this article, we will learn how to automate the tracking and periodic review of user access in your SQL Server environment.

Objective

We will focus on reviewing two aspects of SQL Server Security-

1. Logins with Sysadmin Privileges at the Server Level.
2. Logins with Database-Level Role Assignments.

We will follow the below steps during the tracking and reviewing process-

1. We will create tables to store access details of the logins.
2. Then we will create stored procedures to collect access details, such as Sysadmin role members, database role members and populate the tables.
3. Finally, we will create one more stored procedure to compile the access details and send them via email to concerned stakeholders for periodic review and action.

Create Tables

Note: We will use a database named ‘DBUtility’  as an example to create the necessary tables and stored procedures. Please refer to the DBUtility script to create the database and the default schema before continuing. 

We will create three tables –

1. tbl_SysAdmins – This table stores information about logins that have Sysadmin access at server level.
2. tbl_DBRoleMembers  – This table stores information about Database user’s with their assigned database roles.
3. tbl_WindowsGroupMembers(Optional) – This table stores information about Windows AD Group Members.

   Note: This table is only required, is you have implemented an AD Group-based login mechanism in your environment.

Use the below script to create the tables.

USE [DBUtility]
GO
CREATE TABLE [uar].[tbl_DBRoleMembers](
[ServerName] [sysname] NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[DatabaseRole] [sysname] NULL,
[UserType] [nvarchar](50) NULL,
[MappedLogin] [sysname] NULL
) ON [PRIMARY]
GO

CREATE TABLE [uar].[tbl_SysAdmins](
[ServerName] [sysname] NOT NULL,
[LoginName] [sysname] NOT NULL,
[LoginType] [nvarchar](50) NULL,
[WindowsGroupName] [sysname] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [uar].[tbl_WindowsGroupMembers](
[ServerName] [sysname] NOT NULL,
[GroupName] [sysname] NOT NULL,
[MemberName] [sysname] NOT NULL
) ON [PRIMARY]
GO

Create Stored Procedures

We will create three stored procedures to collect and store the user access information into the respective tables.

1. sp_CollectSysAdminMembers - This procedure collects a list of windows logins that belongs to the specified Sysadmin AD Group, along with SQL and individual Windows logins that are not part of the Group but have Sysadmin privileges. It then inserts the collected information into the tbl_SysAdmins table. It accepts two parameters-
   • @ServerName(Mandatory) – Name of the SQL Server Instance
   • @WindowsGroup((Optional) – Name of the Windows AD group(s) which has sysadmin privilege at SQL Server.
2. sp_CollectDBRoleMembers - This procedure collects a list of database users, their assigned roles, mapped server logins, and the login type(Windows or SQL). It then inserts the collected information into tbl_DBRoleMembers table. It also accepts two parameters-
   • @ServerName(Mandatory) – Name of the SQL Server Instance
   • @DatabaseList(Mandatory) – Name of the Database(s) for which access list needs to be collected.
3. sp_CollectWindowsGrpMembers - This procedure collects a list of windows logins that belong to the specified WindowsGroup(e.g. Sysadmins, Developers, Readers, etc.). It then inserts the collected information into tbl_WindowsGroupMembers table. It accepts-
   • @ServerName(Mandatory) – Name of the SQL Server Instance
   • @WindowsGroup(Mandatory) – Name of the Windows AD group(s) for which member details need to be fetched.

     Note: This stored procedure is only required, if your environment uses an AD Group-based login mechanism.

Data Collection

Now, we will execute the stored procedures one by one, using the below script to collect and store the data for user access review.

USE [DBUtility]
GO
EXEC [DBUtility].[uar].[sp_CollectSysAdminMembers]
@ServerName = 'ServerName', --SQL ServerName
@WindowsGroups = 'DOMAIN\SQLDBAdmins,DOMAIN\SQLManagers' -- AD Group Name

USE [DBUtility]
GO
EXEC [DBUtility].[uar].[sp_CollectWindowsGrpMembers] 
@ServerName = 'ServerName',----SQL ServerName
@WindowsGroups = 'DOMAIN\SQLDBAdmins,DOMAIN\SQLManagers,DOMAIN\SQLDevelopers,DOMAIN\SQLReaders' -- AD Group Name

USE [DBUtility]
GO
EXEC [DBUtility].[uar].[sp_CollectDBRoleMembers]
@ServerName = 'ServerName', --SQL ServerName
@db_list = 'DB1,DB2'--List of Database(s) for which user access details needs to be collected

Then, run a SELECT query on the tables and verify that the data has been populated correctly.

After collecting the data, we need to send it to the relevant stakeholders for review. To do this, we will create one more stored procedure sp_SendUARReport. This stored procedure retrieves the data from the three tables, formats it into an HTML email body, and sends the email to stakeholders for review.

Use the below script to execute this stored procedure-

USE [DBUtility]
GO
EXEC [DBUtility].[uar].[sp_SendUARReport]
@ServerName = 'ServerName',--SQL ServerName
@ServerDescription = 'SQL DB Server',--Short Description about the SQL Server for subject line
@Receipients = '',--Receipient(s) email address who will review the user access
@ccReceipients = NULL,--CC Receipient(s) email address if any.Default Value NULL
@MailProfile = 'DBProfileName'--SQL Server DB Profile Name

The email is sent in a structured HTML format which makes iteasy to read and review. It will include user access details for each section, helping reviewers identify unnecessary or risky access.

To automate the entire process, create one SQL Server Agent job UserAccessReview. This job will execute the stored procedures in 4 different steps-

1. sp_CollectSysAdminMembers to collect Sysadmin information,
2. sp_CollectDBRoleMembers to collect database users access information,
3. sp_CollectWindowsGrpMembers (if applicable) to collect Windows AD Group Members information,
4. sp_SendUARReport to email the report.

You can schedule this job to run every quarter or every six months, depending on your organization's policy.

Full code available at - SQLUserAccessReview

Conclusion

Securing SQL Server is more important than ever in today’s data-driven world. Unauthorized access to data can lead to serious business disruptions and financial losses. That’s why it is eseential to regularly review database user access.

The approach described helps automate the review process, reducing manual effort and helping you maintain consistent oversight.