How can I duplicate or clone my RDS for SQL Server user login permissions?

Resolution

Note: This script doesn't make any changes to new logins unless the user running the script has access to grant those permissions. If the user doesn't have access to grant permissions, then the permissions don't appear in the 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 fails.

1.    Copy and run the MSSQL_RDS_Clone_Login script to create a stored procedure in the environment where you want to duplicate the user. You use this stored procedure later to create a duplicate of the user login, database user, server level, and database level permissions.

You can create the stored procedure in any user database that's not within the Amazon RDS for SQL Server system database. Replace [DB_NAME] with the name of the user database, according to your use case.

The following script creates three stored procedures:

• DuplicateLogin: Duplicates the login and duplicate database user for those logins in each database containing the login permissions that you're copying from.
• GrantUserRoleMembership: Duplicates DB user permissions and roles to the new user.
• DuplicateRDS: Consolidates the results of both the DuplicateLogin and GrantUserRoleMembership stored procedures.

2.    After creating the stored procedure, open a new TSQL window and run the stored procedure using the following format.

Note: Before running the script, make sure that the results are in text format by pressing CTRL+T on your keyboard.

--SQL login
EXEC    DuplicateRDS @NewLogin=[<duplicate_login_name>]
         ,@NewLoginPwd = password
    ,@LoginToDuplicate = master_login
    , @WindowsLogin  = F
    ,@DatabaseName=NULL

-- Windows login
EXEC    DuplicateRDS @NewLogin=[<domain\duplicate_login_name>]
    ,@LoginToDuplicate = master_login
    ,@NewLoginPwd = NULL
    , @WindowsLogin  = T
    ,@DatabaseName=NULL

3.    After the script generates, copy the script from the Results tab and run it in a new query window. After the script runs, your SQL Server login generates with similar server and database level permissions as your master login.

Note: This example excludes SSISDB permissions for ssis_admin and ssis_logreader. 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]
GO

Note: The script out might occur in an unorderly fashion due to temp tables being used in the stored procedure. If this occurs, drop and then recreate the stored procedure to get it into the correct format.

4.    If you have orphan users, run the following script. This script checks for orphan users in databases that the script failed removes those orphan users.

The following script checks for orphan users:

Use [DB_NAME] ; 
GO 
exec sp_change_users_login @Action='Report' ;
GO

The following script drops orphan users:

Use [DB_NAME] ;
GO
exec sp_revokedbaccess 'username'
GO

5.    If you don't want to keep the stored procedures after duplicating the login (for example, due to compliance), then run the following script:

USE [DB_NAME]
GO
DROP PROCEDURE [dbo].[DuplicateRDS]
GO
DROP PROCEDURE [dbo].[DuplicateLogin]
GO
DROP PROCEDURE [dbo].[GrantUserRoleMembership]
GO

---