Introduction

When you create a new Amazon RDS for SQL Server instance, the master user is provisioned with elevated privileges, allowing them to perform most administrative operations permitted within the confines of the RDS managed environment. However, AWS strongly recommends not using the master user directly in applications. Doing so violates the principle of least privilege and creates security and accountability risks. A more sustainable and secure strategy is to create a dedicated login with only the required permissions, tailored to the needs of the application, service, or user role.

This security-first approach is relevant across a wide range of use cases. For instance, instead of embedding the master user’s credentials into application connection strings, it's better to use an application-specific SQL Server login. For teams with multiple DBAs, creating named accounts improves traceability and auditability. Likewise, in service-oriented architectures, using dedicated identities with limited permissions for microservices or batch jobs enforces the least privilege security model across the system.

In this article, we will walk through a solution that allows you to clone the master user's permissions onto a new SQL Server login using a custom stored procedure called usp_rds_clone_login. You can then review and trim these permissions as necessary to comply with your internal policies and minimize attack surface.

Solution Overview

This solution involves several carefully structured steps to ensure that permissions are replicated as closely as possible to the master user, while also providing you with full control over what is granted and how. The process begins by creating a stored procedure (usp_rds_clone_login) inside your RDS SQL Server instance. This procedure, when executed, generates a comprehensive SQL script that includes login creation, server roles, server-level permissions, user creation across databases, database roles, and granular permissions on database-level objects.

After running the procedure, the output is displayed in SSMS’s Results pane. This output contains all the CREATE LOGIN, GRANT, and EXEC statements needed to replicate the master user’s access profile. You can copy this script, paste it into a new SSMS window, review or edit it as needed, and then execute it. This gives you complete visibility into what the cloned user is allowed to do—something that is especially useful when auditing access before granting privileges.

Prerequisites

Before proceeding, ensure that you have:

• An active Amazon RDS for SQL Server instance.
• SQL Server Management Studio (SSMS) is installed and connected to your RDS instance.
• Access via a login that has enough permissions to read and grant server and database-level permissions.

The user executing the stored procedure must have the ability to view and grant the permissions that will be scripted. If the executing user lacks these privileges, the output script will not include those permissions and any manual attempts to run those statements will fail with permission errors.

Download and Create the usp_rds_clone_login Procedure

To get started, download the stored procedure script that facilitates the cloning process. This script was originally shared by AWS in their community documentation and can also be accessed in this article. The download is here: usp_rds_clone_login.sql

Open the script in SSMS, connect to your RDS SQL Server instance, and select or create a user database (for example, DBATools) that is not one of the RDS system databases such as rdsadmin.

CREATE DATABASE DBADB;
GO

USE DBADB;
GO
-- Paste the contents of usp_rds_clone_login.sql here and execute

This procedure performs several tasks automatically during execution:

• It creates a new SQL Server login with the specified password.
• It grants server role membership to the new login, based on the source login.
• It assigns server-level permissions, including access to configuration, metadata, and management views.
• For each user database (excluding system databases), it checks if the source login exists as a user.
• It then creates the database user, assigns database roles, and grants permissions on schemas, tables, views, and objects.

Note: When running the stored procedure, the script does not generate a result unless the user running the stored procedure has access to grant those permissions. If the user doesn’t have access to grant permissions, then the permissions don’t appear in the output script. This is because the login used to access the script doesn’t have view privileges. In addition, if you try to add the permissions script manually when you don’t have grant privileges, the script will fail.

With the procedure in place, execute it using the following syntax, replacing values with your own login names and passwords:

-- SQL server authentication login 
EXEC usp_rds_clone_login @NewLogin = 'AdminClone',
                         @NewLoginPwd = 'Password_for_new_login_here',
                         @LoginToDuplicate = master_login,
                         @WindowsAuth = 0;

-- Windows authentication login 
EXEC usp_rds_clone_login @NewLogin = 'AdminClone',
                         @NewLoginPwd = NULL,
                         @LoginToDuplicate = master_login,
                         @WindowsAuth = 1;

The following is an example output when cloning the admin account into a new domain user account:

EXEC dbo.usp_rds_clone_login @NewLogin = 'MyDomain.com\AdminClone',
                             @NewLoginPwd = NULL,
                             @WindowsAuth = 1,
                             @LoginToDuplicate = 'admin';

The stored procedure will scan all online databases (excluding model, msdb, rdsadmin, and other databases) and script out permission grants for every role and object to which the original login has access. No changes are made to the system until you execute the output script.

Review and Run the Output Script

Once the stored procedure completes, it presents a full list of SQL statements in the Results pane of SSMS. This output includes:

• Login creation statements (CREATE LOGIN)
• Server role grants (sp_addsrvrolemember)
• Server-level GRANT permissions
• CREATE USER commands for each database
• Database role membership grants
• Schema, object, and table-level permission grants

Here’s a sample from the output:

/*Cloning Process Steps*//*==================================================*//*1 - Create new login*//*2 - Server role membership for new login*//*3 - Server level permissions for the new login*//*4 - Create database user for new login*//*5 - Database role membership for db user*//*6 - Database level permissions*//*==================================================*//*1 - Create new login*/CREATE LOGIN [MyDomain.com\AdminClone] FROM WINDOWS;
/*2 - Server role memberships for new login*/EXEC sp_addsrvrolemember @loginame = 'MyDomain.com\AdminClone', @rolename = 'setupadmin';
EXEC sp_addsrvrolemember @loginame = 'MyDomain.com\AdminClone', @rolename = 'processadmin';
/*3 - Server level permissions for the new login*/USE master;GRANT ALTER ANY EVENT SESSION TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ADMINISTER BULK OPERATIONS TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER ANY SERVER AUDIT TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER ANY CONNECTION TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER ANY LOGIN TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER ANY LINKED SERVER TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER ANY SERVER ROLE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER SERVER STATE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT ALTER TRACE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT CREATE ANY DATABASE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT VIEW ANY DEFINITION TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT VIEW ANY DATABASE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
USE master;GRANT VIEW SERVER STATE TO [MyDomain.com\AdminClone] WITH GRANT OPTION;
GRANT ALTER ANY CREDENTIAL TO [MyDomain.com\AdminClone] ;
/*4 - Create database user for new login*/USE [DBADB]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain.com\AdminClone') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain.com\AdminClone', 'MyDomain.com\AdminClone' 
ELSE CREATE USER [MyDomain.com\AdminClone] FROM LOGIN [MyDomain.com\AdminClone];
END;
USE [master]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain.com\AdminClone') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain.com\AdminClone', 'MyDomain.com\AdminClone' 
ELSE CREATE USER [MyDomain.com\AdminClone] FROM LOGIN [MyDomain.com\AdminClone];
END;
USE [msdb]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain.com\AdminClone') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain.com\AdminClone', 'MyDomain.com\AdminClone' 
ELSE CREATE USER [MyDomain.com\AdminClone] FROM LOGIN [MyDomain.com\AdminClone];
END;
.
.
.
.
.

Copy all the commands into a new query window, verify for accuracy, and then execute them. This allows for a manual review of all permissions before applying them—a critical requirement in regulated environments.

Test Scenario: Validation with Application Workload

Once permissions are applied, simulate application behavior using the cloned login. Connect to the RDS SQL Server instance as AdminClone and attempt common administrative tasks such as running diagnostics, inspecting sessions, or managing backups:

SELECT name, state_desc FROM sys.databases;
SELECT session_id, login_name FROM sys.dm_exec_sessions;
SELECT name, state_desc FROM sys.databases;
SELECT session_id, login_name FROM sys.dm_exec_sessions;

Test by running Backup/Restore, which are usually reserved for the RDS admin login

EXEC msdb.dbo.rds_backup_database
    @source_db_name='CustomerDB',
    @s3_arn_to_backup_to='arn:aws:s3:::mybucket/backups/CustomerDB.bak',
    @overwrite_S3_backup_file=1;

If the queries execute successfully and return valid results, the permission cloning was effective. Otherwise, adjust the permissions based on any missing access, and rerun the required parts of the output script.

Conclusion

Cloning a master user’s permissions in Amazon RDS for SQL Server is not just about convenience—it’s about adhering to the principle of least privilege, maintaining audit compliance, and improving operational security. With the custom usp_rds_clone_login procedure, DBAs gain a transparent and reproducible way to mirror access rights, review them, and deploy them in controlled environments. This hybrid approach of automation plus manual oversight makes it ideal for secure production workloads, development automation, and access control in multi-user environments.

References

• AWS Documentation – Amazon RDS for SQL Server User Guide
• AWS S3 Script Download – Download usp_rds_clone_login.sql
• SQL Server Security Best Practices – Using SSL with Amazon RDS