How do I move SQL Server users from one RDS for SQL Server instance to another or to an on-premises environment from RDS?
Last updated: 2022-10-25
I want to migrate SQL Server logins and passwords from one Amazon Relational Database (Amazon RDS) for Microsoft SQL Server instance to another Amazon RDS for SQL Server instance. Or, I want to migrate logins from or to an on-premises environment. How do I do this?
Resolution
Note: For instructions on duplicating the master 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, do the following:
- Take a snapshot of the source instance.
- Restore the snapshot using a different name on the target instance.
- 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 enter passwords manually in the following resolutions. You can't retrieve 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.
2. Run the stored procedure to script out logins on the source instance.
Note: Creating the stored procedure in the master 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'
Option 2
To create the logins manually or to script the logins using Microsoft SQL Server Management Studio (SSMS), do the following:
1. Open the security place holder in SSMS and then expand it.
2. Select the logins and press the F7 key. Or, select Object explorer details from the View menu.
3. Select the logins that you want to migrate by pressing and holding the control key.
4. After selecting the required logins, right-click on the screen and choose Script Login as to open a new query window.
5. Copy the create login script and run it on the destination instance by replacing the passwords. For the stored procedure script, see Method 1 in Transfer logins and passwords between instances of SQL Server.
Migrate the logins and passwords from an on-premises environment to RDS for SQL Server
On the source server (on-premises), do the following:
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.
2. After creating the stored procedure, open a new T-SQL window and run the stored procedure using the following command.
Note: Before running the script, make sure that the results are in text format by pressing CTRL+T on your keyboard.
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, do the following:
1. Connect to the RDS for SQL Server instance using the master user account.
2. Run the script generated in step 2 (output of sp_help_revlogin) in the On the source server (on-premises) section from an on-premises server.
Note: You can't grant sysadmin access to the logins in RDS. So, when you migrate the logins that have sysadmin privilege to RDS, access isn't granted. You must explicitly grant the required permission for those logins.
For a list of permissions available for RDS users, see Master user account privileges.
Troubleshooting master account permissions
Where do I find my master user name?
1. Sign in to the RDS console.
2. In the navigation pane, choose Databases.
3. Choose the RDS DB instance that you want the master user name for.
4. Open the Configuration tab. The master user name is listed in the Availability section.
How do I reset the master user password or restore permissions to the master user?
For information on resetting the master user password, see How do I reset the master user password for my Amazon RDS DB instance?
Did this article help?
Do you need billing or technical support?