Transfer roles and permissions

Question

Hi,

By using sp_help_revlogin, we can transfer the logins and passwords to the secondary replica. How to transfer roles and permissions?

Answer 1

Database roles + permissions are stored in the database itself, no need to transfer anything beside the databases.

Or what for permissions do you mean?

Answer 2

Hi，Chaitanya Kiran

The sp_help_revlogin stored procedure indeed replicates the server role memberships for login names, but only for the predefined fixed server roles.

Therefore, if there are custom server roles, these custom server roles need to be created on the target server first.

Then execute the script generated by sp_help_revlogin, which will include the commands to add the logins to these roles.

For the migration of server role permissions, the following script can be used for your reference.

Obtain the names, types, and creation commands of all SQL Server logins.

SELECT    
sp.name AS LoginName,   
sp.type_desc AS LoginType,   
'CREATE LOGIN [' + sp.name + '] FROM WINDOWS;' AS CreateLoginCommand
FROM    sys.server_principals sp 
WHERE    
sp.type IN ('S', 'U', 'G')    
AND sp.name NOT LIKE '##%' 

Obtain all logged-in server-level permissions and commands：

SELECT 
  sp.name AS LoginName,
  perm.permission_name COLLATE Latin1_General_CI_AS AS PermissionName,
  perm.state_desc COLLATE Latin1_General_CI_AS AS PermissionState,
  CASE perm.state_desc COLLATE Latin1_General_CI_AS
    WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
    WHEN 'GRANT' THEN 'GRANT'
    WHEN 'DENY' THEN 'DENY'
    WHEN 'REVOKE' THEN 'REVOKE'
  END + ' ' + perm.permission_name COLLATE Latin1_General_CI_AS + ' TO [' + sp.name COLLATE Latin1_General_CI_AS + '];' AS PermissionCommand
FROM 
  sys.server_permissions perm
JOIN 
  sys.server_principals sp 
  ON perm.grantee_principal_id = sp.principal_id
WHERE 
  sp.type IN ('S', 'U', 'G') 
  AND sp.name NOT LIKE '##%' 

For the migration of database-level roles and permissions, you can refer to the following article：

https://stackoverflow.com/questions/45776108/sql-users-roles-and-permissions-migration

https://www.sqlshack.com/move-or-copy-sql-logins-with-assigning-roles-and-permissions/

Best Regards,

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.