How do I move SQL Server users from one RDS for SQL Server instance to another or to an on-premises environment from RDS?

4 minute read
0

I want to migrate SQL Server logins and passwords between two Amazon Relational Database (Amazon RDS) for Microsoft SQL Server instances. Or, I want to migrate logins from or to an on-premises environment.

Resolution

Note: For instructions on duplicating the primary user login, see How can I duplicate or clone my RDS for SQL Server user login permissions?

Migrate SQL Server logins from one RDS for SQL Server instance to another

To migrate the logins and passwords to a new target RDS instance, complete the following steps:

  1. Create a snapshot of the source instance.
  2. Use a different name on the target instance to restore the snapshot.
  3. Drop the logins and databases that aren't required.

To migrate logins to an existing target RDS instance, complete the steps in the following section.

Migrate logins from RDS for SQL Server to an on-premises environment

Note: You must manually enter passwords in the following resolutions. You can't retrieve an encrypted password for logins directly from RDS.

Option 1

  1. Create the sp_help_revlogins stored procedure on any user database. For the stored procedure script, see Method 2 in Transfer logins and passwords between instances of SQL Server (on the Microsoft website).
  2. Run the stored procedure to script logins on the source instance.

Note: Creating the stored procedure in the primary database causes the following error:

"Msg 262, Level 14, State 18, Procedure sp_hexadecimal, Line 1 [Batch Start Line 5]CREATE PROCEDURE permission denied in database 'master'"

To avoid this error, create the procedure on one user database. After you create the logins in the target instance, reset the password to avoid a password mismatch.

Option 2

To manually create the logins, or to use Microsoft SQL Server Management Studio (SSMS) to script the logins, complete the following steps:

  1. Open the security place holder in SSMS, and then expand it.
  2. Select the logins, and then press the F7 key. Or, choose Object explorer details from the View menu.
  3. Press and hold the Ctrl key to select the logins that you want to migrate
  4. After you select the required logins, choose (right-click) the screen. Then, choose Script Login as to open a new query window.
  5. Copy the create login script. Then, replace the passwords to run the script on the destination instance. For the stored procedure script, see Method 1 in Transfer logins and passwords between instances of SQL Server (on the Microsoft website).

Migrate the logins and passwords from an on-premises environment to RDS for SQL Server

On the source server (on-premises), complete the following steps:

  1. Generate the create login script for all the logins from the on-premises server. For the stored procedure script, see Method 2 in Transfer logins and passwords between instances of SQL Server (on the Microsoft website).
  2. After creating the stored procedure, open a new T-SQL window. Then, use the following command to run the stored procedure.

Note: Before you run the script, press Ctrl+T on your keyboard to make sure that the results are in text format.

EXEC [sp_help_revlogin]

This query output provides the script to create the logins that have the original SID and the password on the destination server.

On the destination server, complete the following steps:

  1. Use the primary user account to connect to the RDS for SQL Server instance.
  2. Run the script that you generated in step 2 (output of sp_help_revlogin) in the On the source server (on-premises) section.

Note: You can't grant sysadmin access to the logins in RDS. So, when you migrate the logins that have sysadmin privilege to RDS, they aren't automatically granted access. You must explicitly grant the required permission for those logins.
For a list of available permissions for RDS users, see Primary user account privileges.

Troubleshoot primary account permissions

Find your primary user name

  1. Open the RDS console.
  2. In the navigation pane, choose Databases.
  3. Choose the RDS DB instance that you want the primary user name for.
  4. Open the Configuration tab. The primary user name is listed in the Availability section.

Reset the primary user password, or restore permissions to the primary user

For information on resetting the primary user password, see How do I reset the primary user password for my Amazon RDS DB instance?

2 Comments

I like this note

Creating the stored procedure in the master database causes the following error

But how to overcome it???

Misha
replied 9 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 9 months ago