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

Resolution

Create stored procedures

To duplicate your RDS for SQL Server, you must first create stored procedures in the environment where you want to duplicate the user.

To create the stored procedures, run the MSSQL_RDS_Clone_Login script. For more information, see MSSQL_RDS_Clone_Login script on the GitHub website. You can create the stored procedures in any user database that's not within the RDS for SQL Server system database.

The MSSQL_RDS_Clone_Login script can't make changes to new logins unless the user who runs the script has access to grant those permissions. If the user doesn't have access to grant permissions, then the permissions don't show in the script. If you don't have grant permissions and try to manually add the permissions to the script, then the script fails.

The MSSQL_RDS_Clone_Login script creates three stored procedures:

• DuplicateLogin: This procedure duplicates the login and database user from the login permissions of each database that you're copying from.
• GrantUserRoleMembership: This procedure duplicates DB user permissions and roles to the new user.
• DuplicateRDS: This procedure consolidates the results of both the DuplicateLogin and GrantUserRoleMembership stored procedures.

Run the stored procedures

To run the stored procedures, complete the following steps:

1. Open a new Transact-SQL (TSQL) window.

2. Press CTRL+T to make sure that the results are in text format.

3. Run the following scripts.
   SQL login:

   USE [DB_NAME]   
   EXEC    DuplicateRDS @NewLogin=[duplicate_login_name]  
       ,@NewLoginPwd = password  
       ,@LoginToDuplicate = primary_login  
       ,@WindowsLogin  = F  
       ,@DatabaseName=NULL

   Note: Replace DB_NAME with the name of the user database, duplicate_login_name with your login name, and password with your password.
   Windows login:

   USE [DB_NAME]   
   EXEC    DuplicateRDS @NewLogin=[domain\duplicate_login_name]  
       ,@LoginToDuplicate = primary_login  
       ,@NewLoginPwd = NULL  
       ,@WindowsLogin  = T  
       ,@DatabaseName=NULL

   Note: Replace DB_NAME with the name of the user database, domain with your domain name, and duplicate_login_name with your login name.

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

5. The generated login excludes Microsoft SQL Server Integration Services (SSIS) SSISDB permissions for ssis_admin and ssis_logreader. If you require these permissions, then run the following command:

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

   Note: Replace mydomain with your domain name and user_name with your username. You might receive a disordered script output because of temporary tables that the stored procedure uses. If this occurs, then drop and recreate the stored procedure.

6. To check for orphan users, run the following script:

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

   Note: Replace DB_NAME with the name of the user database.

7. To drop orphan users, run the following script:

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

   Note: Replace DB_NAME with the name of the user database and username with your username.

8. If you don't want to keep the stored procedures after you duplicate the login, then run the following script:

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

   Note: Replace DB_NAME with the name of the user database.