How to clone master user permissions in Amazon RDS for SQL Server

When you create a new Amazon Relational Database Service (Amazon RDS) for SQL Server instance, the master user is granted certain privileges for that database instance. We strongly recommend that you do not use the master user directly in your applications. Instead, adhere to the principles of least privilege and best practices by using a database user created with the minimal permissions required for your application.

This approach is applicable in the following use cases:

Using an application-specific SQL Server login instead of the master user
Using named accounts for database administrators (DBAs) for security and accountability
Implementing the least privilege security model with specific named accounts for applications and services connecting to Amazon RDS for SQL Server

In this post, we discuss how to clone the master user to a new login and review it for the minimal permissions required. You can remove the permissions that are not necessary for the application to implement the least privilege security model.

Solution overview

In this solution, we complete the following steps to clone the master user:

Create a stored procedure called usp_rds_clone_login in the environment where you want to duplicate the user. This can be accomplished by connecting to your RDS SQL Server instance using SQL Server Management Studio (SSMS).
Run the stored procedure to generate the T-SQL script with permissions scripted out as of the moment of executing the stored procedure.
Copy the script from the Results pane in SSMS and run it in a new query window.

After the stored procedure runs, your “create login” script gets generated with similar server-level and database-level permissions as the master login.

Prerequisites

Before cloning a master user in your RDS instance, you must have the following in-place:

Amazon RDS for SQL Server instance
SSMS with connectivity to the database
A user with the required privileges

Create a stored procedure

Create a stored procedure called usp_rds_clone_login ( Download ) in the environment where you want to duplicate the user. You will use this stored procedure in the next step to create a clone of specific login account, database user, server-level permissions, and database-level permissions. You can create the stored procedure in any user database that is not within the Amazon RDS for SQL Server system database.

As part of the process, the script performs the following actions:

Create a new login with the provided password
Assign server role membership for the new login
Assign server-level permissions for the new login
Create a database user for the new login as per LoginToDuplicate
Assign a database role membership for the new user as per LoginToDuplicate
Assign database-level permissions for the new user as per LoginToDuplicate

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.

Run the stored procedure

After you create the stored procedure, open a new T-SQL window and run the stored procedure using the following format. Before running the script, make sure that the results are in text format by pressing CTRL + T on your keyboard.

-- SQL server authentication login 
EXEC usp_rds_clone_login @NewLogin = [<duplicate_login_name>],
                         @NewLoginPwd = 'Password_for_new_login_here',
                         @LoginToDuplicate = master_login,
                         @WindowsAuth = 0;

-- Windows authentication login 
EXEC usp_rds_clone_login @NewLogin = [<domain\duplicate_login_name>],
                         @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\MyDomainUser',
                             @NewLoginPwd = NULL,
                             @WindowsAuth = 1,
                             @LoginToDuplicate = 'admin';

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\MyDomainUser] FROM WINDOWS;
/*2 - Server role memberships for new login*/
EXEC sp_addsrvrolemember @loginame = 'MyDomain\MyDomainUser', @rolename = 'setupadmin';
EXEC sp_addsrvrolemember @loginame = 'MyDomain\MyDomainUser', @rolename = 'processadmin';
/*3 - Server level permissions for the new login*/
USE master;GRANT ALTER ANY EVENT SESSION TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ADMINISTER BULK OPERATIONS TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER ANY SERVER AUDIT TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER ANY CONNECTION TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER ANY LOGIN TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER ANY LINKED SERVER TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER ANY SERVER ROLE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER SERVER STATE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT ALTER TRACE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT CREATE ANY DATABASE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT VIEW ANY DEFINITION TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT VIEW ANY DATABASE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE master;GRANT VIEW SERVER STATE TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
GRANT ALTER ANY CREDENTIAL TO [MyDomain\MyDomainUser] ;
/*4 - Create database user for new login*/
USE [DBATools]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain\MyDomainUser') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain\MyDomainUser', 'MyDomain\MyDomainUser' 
ELSE CREATE USER [MyDomain\MyDomainUser] FROM LOGIN [MyDomain\MyDomainUser];
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\MyDomainUser') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain\MyDomainUser', 'MyDomain\MyDomainUser' 
ELSE CREATE USER [MyDomain\MyDomainUser] FROM LOGIN [MyDomain\MyDomainUser];
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\MyDomainUser') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain\MyDomainUser', 'MyDomain\MyDomainUser' 
ELSE CREATE USER [MyDomain\MyDomainUser] FROM LOGIN [MyDomain\MyDomainUser];
END;
USE [rdsadmin]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain\MyDomainUser') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain\MyDomainUser', 'MyDomain\MyDomainUser' 
ELSE CREATE USER [MyDomain\MyDomainUser] FROM LOGIN [MyDomain\MyDomainUser];
END;
USE [tempdb]; 
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'admin')
BEGIN
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'MyDomain\MyDomainUser') 
EXEC sys.sp_change_users_login 'Update_One', 'MyDomain\MyDomainUser', 'MyDomain\MyDomainUser' 
ELSE CREATE USER [MyDomain\MyDomainUser] FROM LOGIN [MyDomain\MyDomainUser];
END;
/*5 - Database role membership for db user*/
USE [DBATools]; EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'MyDomain\MyDomainUser';
USE [msdb]; EXEC sp_addrolemember @rolename = 'SQLAgentUserRole', @membername = 'MyDomain\MyDomainUser';
/*6 - Database level permissions*/
USE [DBATools]; DENY BACKUP DATABASE ON DATABASE::[DBATools] TO [MyDomain\MyDomainUser];
USE [DBATools]; DENY BACKUP LOG ON DATABASE::[DBATools] TO [MyDomain\MyDomainUser];
USE [msdb]; GRANT ALTER ANY USER ON DATABASE::[msdb] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_restore_tde_certificate] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_task_status] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_tlog_copy_setup] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_tlog_backup_copy_to_S3] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_output] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_task_status] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_shrink_tempdbfile] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_cdc_disable_db] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_cdc_enable_db] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_tuninglog] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_get_audit_file] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_download_from_s3] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_database] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_delete_from_filesystem] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_gather_file_details] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_list_file_details] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_add_profile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_partitionfunction] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_sqlagent_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_update_profile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_msbi_task] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_profile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_upload_to_s3] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_profile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_partitionscheme] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_msdtc_transaction_tracing] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_drop_ssrs_databases] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_drop_ssis_database] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_table] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_failover_time] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_add_account_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_update_account_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_changedbowner_to_rdsa] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_account_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_account_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_tableview] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_add_profileaccount_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_purge_jobhistory] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_update_profileaccount_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_query] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_dms_tlog_download] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_profileaccount_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_dms_tlog_list_current_lsn] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_profileaccount_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_dms_tlog_read] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_querytable] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_configure_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_add_principalprofile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_querydatabase] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_update_principalprofile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_principalprofile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_principalprofile_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_index] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_status_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_help_queue_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_queryindex] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_column] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_indexcolumn] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_send_dbmail] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_reports_querycolumn] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_delete_database_backuphistory] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysjobhistory] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysjobs] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysjobactivity] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_add_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_delete_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_update_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_grant_login_to_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_revoke_login_from_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_enum_proxy_for_subsystem] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_sysmail_control] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sp_enum_login_for_proxy] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_sysmail_allitems] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_sysmail_event_log] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_sysmail_mailattachments] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_sysmail_delete_mailitems_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_allitems] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_server_object_last_sync_time] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_sentitems] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_get_system_database_sync_objects] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_unsentitems] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_set_system_database_sync_objects] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_faileditems] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_input] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_mailitems_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_backup_database] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_backup_tde_certificate] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_mailattachments] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_cancel_task] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_drop_tde_certificate] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[sysmail_event_log] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_finish_restore] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[sysmail_delete_log_sp] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_list_tlog_backup_metadata] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[rds_fn_list_user_tde_certificates] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT SELECT ON OBJECT::[dbo].[DTA_progress] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_restore_database] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT EXECUTE ON OBJECT::[dbo].[rds_restore_log] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT ALTER ON ROLE::[SQLAgentUserRole] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [msdb]; GRANT ALTER ON ROLE::[SQLAgentOperatorRole] TO [MyDomain\MyDomainUser] WITH GRANT OPTION;
USE [tempdb]; GRANT CONTROL ON DATABASE::[tempdb] TO [MyDomain\MyDomainUser];

Copy the outputted script and run it

After the script generates an output, copy the outputted script from the Results tab in SSMS, and run it from a new query window. After the script runs, your new login account gets created with similar server-level and database-level permissions as your master login.

This example excludes ssis_admin and ssis_logreader permissions in the SSISDB database. If you require these permissions, provide them separately:

ALTER ROLE [ssis_admin] ADD MEMBER [mydomain\user_name];
ALTER ROLE [ssis_logreader] ADD MEMBER [mydomain\user_name];

Clean up

If you don’t want to keep the stored procedure after duplicating the login (for example, due to compliance), you can use the following script drop it.

USE [DB_NAME]
GO
DROP PROCEDURE [dbo].[usp_rds_clone_login];
GO

Summary

In this post, we discussed how you can clone the master user in Amazon RDS for SQL Server to a new login. We also covered some key considerations and best practices to avoid using the master user in your application. This solution can help you create a new login with least privileges required for your business needs.

If you have any additional insights, experiences, or concerns regarding this topic, we encourage you to share them in the comments section.

About the authors

Alvaro Costa-Neto is a Database Specialist Solutions Architect for AWS, where he helps customers design and implement database solutions on the cloud. He has a passion for database technologies and has been working with them for more than 19 years, mostly with Microsoft SQL Server. He resides in Clermont, FL, with his wife and two children, who share his love for aviation and traveling. When he is not working, he likes to host cookouts with his family and friends and explore new places.

Rakesh Ramanukolanuis Senior Database Specialist Solutions Architect at Amazon Web Services. He helps customers across different industry verticals design, migrate, and optimize their SQL Server workloads to a managed database platform like Amazon RDS and Amazon RDS Custom.

Mesgana Gormley is a Senior Database Specialist Solution Architect at Amazon Web Services. She works on the Amazon RDS team providing technical guidance to AWS customers and helping them migrate, design, deploy, and optimize relational database workloads on AWS.

AWS Database Blog