AWS Database Blog

Migrate logins, database roles, users and object-level permissions to Amazon RDS for SQL Server using T-SQL

In this post, we explain how to migrate the logins, database roles, users, and object-level permissions from on-prem or Amazon Elastic Compute Cloud (Amazon EC2) for SQL Server to Amazon Relational Database Service (Amazon RDS) for SQL Server using the T-SQL.

When it comes to gaining access to SQL Server, there is a need for an SQL Login which is used for authentication and a database user which is used for authorization. Authentication checks whether you have permission to log in to a system, whereas authorization specifies what you can do once connected. In Microsoft SQL Server, a login is created at the instance level, and a user is created at the database level. Logins can be local SQL Server users or Windows users or groups, including Active Directory users or groups.

When you use SQL Server native backup and restore to migrate an SQL Server Instance along with your databases, everything is migrated, including the username and permissions.

In some of the migration scenarios where native backup and restore is not used as the migration option, when you move one or more selected databases from one SQL Server instance to another, logins and users’ permissions of the objects in the databases are not migrated. Also in the scenarios, when there are requirements to create a database at the destination side in advance, you must migrate logins with passwords and users with object-level permissions from one SQL Server instance to another separately. With that the application can seamlessly access new target databases and objects, with the same permissions as source without a break.

Use Cases

  1. AWS DMS with a full load for homogenous migrations.
  2. Migrating the databases from the SQL Server instance on server A to the instance on server B.

Solution Overview

For the scenarios where logins and users permissions are not migrated, we generate dynamic T-SQL scripts to create the logins, roles, users and object-level permissions for single and multiple databases.

The following diagram illustrates the high-level architecture of migrating logins and database users from on-premises or Amazon EC2 for SQL Server to Amazon RDS for SQL Server.

Prerequisites

To get started, you need to have the following setup

  • An on-premises SQL Server instance or Amazon EC2 for SQL Server instance
  • An Amazon RDS for SQL Server DB instance (Single-AZ or Multi-AZ)
  • Connectivity between an on-premise SQL Server or SQL Server on Amazon EC2 for SQL Server and Amazon RDS for SQL Server
  • In a source server, a login that is part of an ALTER ANY LOGIN server level securable permission can run the statement from the sys.server_principals and sys.server_permissions.
  • A target Amazon RDS for SQL Server database with required user-defined objects (tables, views, functions, and stored procedures) and a db_owner with the required permissions to create objects in SQL Server database
  • A login that is part of processadmin, public, and setupadmin fixed server roles in Amazon RDS for SQL Server instance.

1. Migrate SQL Server Logins, server roles and Server level grant permissions to Amazon RDS for SQL Server

To migrate the logins from SQL Server running on EC2 or on-premises to Amazon RDS for SQL Server, run the following steps to script out the create login scripts with a hash password with SID. The output script generates the create login script with password hash and SID.

Migrate logins

This section explains how to generate the create logins script from a source SQL Server instance and migrate it to the target Amazon RDS for SQL Server.

  1. Connect to your source SQL Server instance (Amazon EC2 or On-premises) as a local administrator using Remote Desktop Protocol (RDP).
  2. Connect to the SQL Server instance using SQL Server Manager Studio (SSMS)
  3. In the SSMS query editor, select the Results to Grid (selecting Ctrl + D) option
  4. Run the following script from the new query window to generate the create login script. The output of the query provides the logins creation script.
SET NOCOUNT ON
SELECT ' IF (SUSER_ID('+QUOTENAME(srvprn.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(srvprn.name)+ 
CASE 
WHEN srvprn.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),sqllogin.password_hash,1)+ ' HASHED, SID =' + CONVERT(NVARCHAR(MAX),srvprn.sid,1)+', CHECK_EXPIRATION = ' 
+ CASE WHEN sqllogin.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN sqllogin.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END 
ELSE ' FROM WINDOWS WITH' 
END 
+' DEFAULT_DATABASE=[' +srvprn.default_database_name+ '], DEFAULT_LANGUAGE=[' +srvprn.default_language_name+ '] END;' 
AS [-- Amazon RDS Logins Creation Script --] 
FROM sys.server_principals AS srvprn LEFT JOIN sys.sql_logins AS sqllogin 
ON srvprn.principal_id = sqllogin.principal_id 
-- Only include login types ('S','G','U') 
WHERE srvprn.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN') 
-- exclude Windows local service accounts 
AND srvprn.name NOT LIKE 'NT SERVICE%' 
AND srvprn.name NOT LIKE 'NT AUTHORITY%'
AND srvprn.name NOT LIKE '%\Administrator%'
AND srvprn.name NOT LIKE 'distributor_admin'
AND srvprn.name NOT LIKE '##%##' 
AND srvprn.name <> ('sa');

     Note:

  • You can ignore the SQL Server Reporting Services (SSRS) related service account logins as they need to be handled in a different way in Amazon RDS for SQL Server. For configuring SSRS in Amazon RDS for SQL Server, refer to Support for SQL Server  Reporting Services in Amazon RDS for SQL Server.
  • Exclude any other logins not required on target Amazon RDS for SQL Server.
  1. Connect to the target Amazon RDS for SQL Server using SQL Server Manager Studio (SSMS) and copy the required logins script, generated in step 4.

       Note: Be responsible and cautious while copying the output and pasting it at the target database, as it contains sensitive data.

  1. Run the copied script using the RDS master user or a login with “Alter any login” server level securable that is part of a processadmin, and setupadmin fixed server roles (refer the section: Microsoft SQL Server Security) in Amazon RDS for SQL Server instance.
  2. Validate the script execution output for any errors.

After the successful migration of logins, continue with the server role membership migration.

Migrate server role membership

This section explains how to generate the create server role membership script from a source SQL Server instance and migrate it to the target Amazon RDS for SQL Server.

  1. Connect to your source SQL Server instance (Amazon EC2 or On-premises) as a local administrator using Remote Desktop Protocol (RDP).
  2. Connect to the SQL Server instance using SQL Server Manager Studio (SSMS)
  3. In the SSMS query editor, select the Results to Grid (selecting Ctrl + D) option
  4. Run the following script from the new query window to generate the server role membership script. The output of the query provides the server role membership creation script.
SET NOCOUNT ON
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SrvPL.name + ''', @rolename = N''' + SrvPR.name + ''' 
' AS [-- SQL Server Roles --] 
FROM master.sys.server_role_members SrvRM 
JOIN master.sys.server_principals SrvPR ON SrvPR.principal_id = SrvRM.role_principal_id 
JOIN master.sys.server_principals SrvPL ON SrvPL.principal_id = SrvRM.member_principal_id 
-- Only include login types ('S','G','U')
WHERE SrvPL.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN') 
-- Amazon RDS allowed server roles
and SrvPR.name in('setupadmin', 'processadmin')
AND SrvPL.name NOT LIKE 'NT SERVICE%' 
AND SrvPL.name NOT LIKE 'NT AUTHORITY%' 
AND SrvPL.name NOT LIKE '%\Administrator%' 
AND SrvPL.name NOT LIKE '##%##' 
AND SrvPL.name NOT LIKE 'NT AUTHORITY%' 
AND SrvPL.name NOT LIKE 'distributor_admin' AND SrvPL.name != ('sa');
  1. Connect to the target Amazon RDS for SQL Server using SQL Server Manager Studio (SSMS) and copy the required logins script, generated in step 4.
  2. Run the copied script using the RDS master user or a login with “Alter any login” server level securable that is part of a processadmin, and setupadmin fixed server roles(refer the section: Microsoft SQL Server Security) in Amazon RDS for SQL Server instance.

Note: With Amazon RDS for SQL Server level permissions limitations, an user can only grant “setupadmin”, “processadmin” server level roles for logins or groups.

  1. Validate the script execution output for any errors.

After the successful migration of the server role membership, continue with the server level grant permission migration.

Migrate server level grant permissions

This section explains how to generate the create server level grant permissions script from the source SQL Server instance and migrate it to the target Amazon RDS for SQL Server.

  1. Connect to your source SQL Server instance (Amazon EC2 or On-premises) as a local administrator using Remote Desktop Protocol (RDP).
  2. Connect to the SQL Server instance using SQL Server Manager Studio (SSMS)
  3. In the SSMS query editor, select the Results to Grid (selecting Ctrl + D) option
  4. Run the following script from the new query window to generate the create login script. The output of the query provides the server level grant permissions creation script.
SELECT 
CASE WHEN SrvPerm.state_desc != 'GRANT_WITH_GRANT_OPTION' 
THEN 'IF (SELECT SUSER_NAME()) != '+CHAR(39)+ SrvP.name +CHAR(39)+ ' Begin '+ CHAR(10)+SrvPerm.state_desc 
ELSE 'GRANT'  END  + ' ' + SrvPerm.permission_name + ' TO [' + SrvP.name + ']'+' end;' + 
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
THEN '' 
ELSE ' WITH GRANT OPTION'  END collate database_default AS [-- Server Level Grant Permissions --] 
FROM sys.server_permissions AS SrvPerm 
JOIN sys.server_principals AS SrvP ON SrvPerm.grantee_principal_id = SrvP.principal_id 
-- Only include login types ('S','G','U')
WHERE SrvP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
AND SrvP.name NOT LIKE 'NT SERVICE%' AND SrvP.name NOT LIKE 'NT AUTHORITY%' 
AND SrvP.name NOT LIKE '%\Administrator%' AND SrvP.name NOT LIKE '##%##' 
AND SrvP.name != ('sa')
--Amazon RDS Server level grant permissions exclusion list, Amazon RDS does not support  
AND SrvPerm.permission_name NOT IN('ALTER ANY AVAILABILITY GROUP',
'ALTER ANY DATABASE',
'ALTER ANY ENDPOINT',
'ALTER ANY EVENT NOTIFICATION',
'ALTER RESOURCES',
'ALTER SETTINGS',
'AUTHENTICATE SERVER',
'CONNECT ANY DATABASE',
'CONTROL SERVER',
'CREATE AVAILABILITY GROUP',
'CREATE DDL EVENT NOTIFICATION',
'CREATE ENDPOINT',
'CREATE SERVER ROLE',
'CREATE TRACE EVENT NOTIFICATION',
'EXTERNAL ACCESS ASSEMBLY',
'IMPERSONATE ANY LOGIN',
'SELECT ALL USER SECURABLES',
'SHUTDOWN',
'UNSAFE ASSEMBLY')
  1. Connect to the target Amazon RDS for SQL Server using SQL Server Manager Studio (SSMS) and copy the required logins script, generated in step 4.
  2. Run the copied script using the RDS master user or a login with “Alter any login” server level securable that is part of a processadmin, and setupadmin fixed server roles(refer the section: Microsoft SQL Server Security) in Amazon RDS for SQL Server instance.
  3. Validate the script execution output for any errors.

2. Migrate SQL Server database users, roles and object-level permissions to Amazon RDS for SQL Server

To migrate the database users, roles and object-level permissions from Amazon EC2 for SQL Server or on-premises to Amazon RDS for SQL Server, complete the following steps to script out the create users, roles and object-level permission scripts.

Migrate database users, roles, role membership and object-level permissions for single or multiple databases.

For a Single Database

This section explains how to generate the users, roles, role membership and object-level permissions for a single database using the T-SQL script from a source SQL server instance and migrate to target Amazon RDS for SQL Server.

  1. Connect to your source SQL Server instance (Amazon EC2 or On-premises) as a local administrator using Remote Desktop Protocol (RDP).
  2. Connect to SQL Server instance using SQL Server Manager Studio (SSMS)
  3. In the SSMS query editor, select the Results to Text (selecting Ctrl + T) option
  4. Run the following script from the new query window to generate the users, roles, role membership and object-level permissions create script.
USE <database_name>
 
 SET NOCOUNT ON
-- Migrate Database Users
SELECT 
' Use '+DB_Name()+'; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '+char(39) + name + char(39)+')
Exec sp_grantdbaccess '+char(39)+suser_sname(sid)+char(39)+', '+char(39)+name+char(39)+';' +''  from sys.database_principals where principal_id>4 and type in('S', 'U')
AND suser_sname(sid) IS NOT NULL
  
-- Migrate the user-defined database roles
;WITH CTE_role as  (
SELECT  
 DBP1.name as 'Role_name' 
 FROM sys.database_role_members AS DBRM
 RIGHT OUTER JOIN sys.database_principals AS DBP1
 ON DBRM.role_principal_id = DBP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DBP2
 ON DBRM.member_principal_id = DBP2.principal_id
WHERE DBP1.type = 'R'
and DBP1.is_fixed_role=0 and
DBP1.name<>'public' ) 
SELECT 'Use '+ db_name() +'; IF NOT EXISTS (SELECT NAME FROM sys.database_principals where name = '''+Role_name+''') CREATE ROLE '+Role_name +'; '   from CTE_role
 
-- Database role membership
select  'Exec sp_addrolemember '''+ user_name(DBRM.role_principal_id)+ ''','
+ '''' + user_name(DBRM.member_principal_id)+''''+ '; ' from sys.database_role_members DBRM inner join sys.database_principals DBP on DBRM.member_principal_id=DBP.principal_id where DBRM.member_principal_id>1 
 
-- Migrate the Database object-level permissions 
Select state_desc + ' ' + permission_name + ' ON [' + SCHEMA_NAME(SysObj.schema_id) + '].['+OBJECT_NAME(DBP.major_id)
+'] TO [' + USER_NAME(DBP.grantee_principal_id) + ']'+ '; ' [--Command to add object-level Permissions--] 
from sys.database_permissions DBP INNER JOIN sys.database_principals DBPS ON DBP.grantee_principal_id=DBPS.principal_id Inner Join sys.objects SysObj ON SysObj.object_id=DBP.major_id where DBPS.name not in ('public','Guest');

Note: Make sure the script is executed for the <database_name>.

  1. Connect to the target Amazon RDS for SQL Server using SQL Server Manager Studio (SSMS) and copy the output scripts generated in step 4.
  2. Run the copied script using a login that is part of a db_owner role for that database in the Amazon RDS for SQL Server instance.
  3. Validate the script execution output for any errors.

For Multiple User Databases

This section explains how to generate the users, roles, role membership and object-level permissions for multiple user-defined databases using T-SQL script from source SQL server instance and migrate to target Amazon RDS for SQL Server.

  1. Connect to your source SQL Server instance (Amazon EC2 or On-premises) as a local administrator using Remote Desktop Protocol (RDP).
  2. Connect to the SQL Server instance using SQL Server Manager Studio (SSMS)
  3. In the SSMS query editor, select the Results to Text (selecting Ctrl + T) option
  4. Run the following script from the new query window to generate the users, roles, role membership and object-level permissions create script
--  Migrate Database Users
 
DECLARE @command nvarchar(4000)
-- All System, Report server and SSISDB databases are excluded 
SELECT @command = ' Use [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempDB'' AND ''?'' <> ''SSISDB''   SELECT 
'' USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''+char(39) + name + char(39)+'')
Exec sp_grantdbaccess ''+char(39)+suser_sname(sid)+char(39)+'', ''+ char(39) + name + char(39)+ ''; '' +''''  from sys.database_principals where principal_id>4 and type in(''S'', ''U'')
AND suser_sname(sid) IS NOT NULL' EXEC sp_MSforeachdb @command 
 
 
-- Migrate the user-defined database roles 
 
--A. Create User-defined Database Role Script
DECLARE @command1 nvarchar(4000)
-- All System, Report server and SSISDB databases are excluded
SELECT @command1 = 'Use [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempdb'' AND ''?'' <> ''SSISDB''
SELECT ''USE [?]; IF NOT EXISTS (select 1 from sys.database_principals where name=''+char(39)+
 		DBP1.name + char(39)+ '') CREATE ROLE ''+
 		DBP1.name + ''; ''
 		FROM sys.database_role_members AS DBRM
 		RIGHT OUTER JOIN sys.database_principals AS DBP1
  		ON DBRM.role_principal_id = DBP1.principal_id
 		LEFT OUTER JOIN sys.database_principals AS DBP2
  		ON DBRM.member_principal_id = DBP2.principal_id
WHERE DBP1.type = ''R''
and DBP1.is_fixed_role=0 and
DBP1.name<>''public''
ORDER BY DBP1.name'
 
EXEC sp_MSforeachdb @command1 
 
 
-- B. Generate add database role membership
DECLARE @command2 nvarchar(4000)
-- All System databases are excluded 
SELECT @command2 = 'Use [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempdb'' AND ''?'' <> ''SSISDB''
select 
''USE [?]; Exec sp_addrolemember '''+'+char(39)+'+ 'user_name(DBRM.role_principal_id)' +'+char(39)+'+''','' +CHAR(39)+' + 'user_name(DBRM.member_principal_id)'+'+ char(39) + ''; ''
from sys.database_role_members DBRM
inner join sys.database_principals DBP on DBRM.member_principal_id=DBP.principal_id where DBRM.member_principal_id>1 '
EXEC sp_MSforeachdb @command2 
 
 
--  Migrate the Database object-level permissions 
 
DECLARE @command3 nvarchar(4000)
SELECT @command3 = 'Use [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempdb'' AND ''?'' <> ''SSISDB''
select ''use [?]''+'';'' +state_desc +char(32)+ permission_name +'' ON [''+ SCHEMA_NAME(SysObj.schema_id)+ ''].[''+OBJECT_NAME(DBP.major_id)
+''] TO ['' + USER_NAME(DBP.grantee_principal_id) + '']'' + ''; ''
from sys.database_permissions DBP
INNER JOIN sys.database_principals DBPS
ON DBP.grantee_principal_id=DBPS.principal_id
Inner Join sys.objects SysObj ON SysObj.object_id=DBP.major_id
where DBPS.name not in (''public'',''Guest'')'
EXEC sp_MSforeachdb @command3
  1. Connect to the target Amazon RDS for SQL Server using SQL Server Manager Studio (SSMS) and copy the output scripts generated in step 4.
  2. Run the copied script using a login that is part of a db_owner role for that database in the Amazon RDS for SQL Server instance.
  3. Validate the script execution output for any errors

Migrate sysadmin logins as db_owner in Amazon RDS for SQL Server

As per Amazon RDS for SQL Server level permission limitations, we cannot grant sysadmin privileges to the users at the target. In the case of granting db_owner permission to the source sysadmin users for user-defined databases, run the following script.

DECLARE @command nvarchar(4000)
SELECT @command = 'Use [?]
-- Ignoring system, reporting (SSRS) and integration services(SSIS) databases
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''  AND ''?'' <> ''ReportServer''   AND ''?'' <> ''ReportServerTempDB''   AND ''?'' <> ''SSISDB'' 
select ''USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''+char(39) + SrvPL.name + char(39)+'')  Exec sp_grantdbaccess '' + char(39) + SrvPL.name + char(39) + '',''+ char(39) +  SrvPL.name + char(39) +'';''
+ '' Exec sp_addrolemember '' +  char(39)+ ''db_owner''+char(39) +'',''+char(39)+ SrvPL.name + char(39)+'';'' AS [-- SQL Server Roles --] 
FROM master.sys.server_role_members SrvRM 
JOIN master.sys.server_principals SrvPR ON SrvPR.principal_id = SrvRM.role_principal_id 
JOIN master.sys.server_principals SrvPL ON SrvPL.principal_id = SrvRM.member_principal_id 
-- Only include login types (''S'',''G'',''U'')
WHERE SrvPL.type_desc IN (''SQL_LOGIN'',''WINDOWS_GROUP'',''WINDOWS_LOGIN'') 
-- Amazon RDS allowed server roles
and SrvPR.name in(''sysadmin'')
AND SrvPL.name NOT LIKE ''NT SERVICE%'' 
AND SrvPL.name NOT LIKE ''NT AUTHORITY%'' 
AND SrvPL.name NOT LIKE ''%\Administrator%'' 
AND SrvPL.name NOT LIKE ''##%##'' 
AND SrvPL.name NOT LIKE ''NT AUTHORITY%'' 
AND SrvPL.name NOT LIKE ''distributor_admin'' AND SrvPL.name != (''sa'')'

EXEC sp_MSforeachdb @command

Clean up the environment

To avoid future charges, remove all of the components created while testing this use case by completing the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Select the databases you set up and on the Actions menu, choose Delete.
  3. Enter delete me to confirm deletion.
    For more information about deleting an instance, refer to Deleting a DB instance.
  4. On the Amazon EC2 console, locate the SQL Server on Amazon EC2 instance that you used as your source and delete the instance.

Conclusion

In this post, we described how to migrate the logins, database roles, users and object-level permissions to Amazon RDS for SQL Server using T-SQL. Give it a try, and let us know what you think by commenting on this post. We’re always looking forward to your feedback, either through your usual AWS support contacts, or on the AWS Forum for Amazon RDS.

About the authors


Ramesh Babu Donti, a Lead Database Consultant with AWS Professional Services based out of Hyderabad, India. He focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale databases to AWS.

Sai Krishna Namburu, a Lead Database Consultant with AWS Professional Services based out of Hyderabad, India. With good knowledge on relational databases adding hands-on in homogenous and heterogenous database migrations, he helps the customer’s migration to AWS cloud and their optimisations.

Jinesh Shah, a Database Consultant with AWS Professional Services, based in Texas, USA. He works with customers in their journey to the cloud and helps them successfully achieve cost-effective, scalable, optimized, secure and highly-available database migration and modernization solutions.

Indu Shekhar, a Database Consultant with AWS Professional Services based out of Hyderabad, India.With good knowledge on relational databases adding hands-on in homogenous and heterogenous database migrations, helps customers in migrating to AWS cloud and their optimisations.