Transfer roles and permissions

Chaitanya Kiran 776 Reputation points


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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,095 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 42,486 Reputation points

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

    Or what for permissions do you mean?

    0 comments No comments

  2. MikeyQiaoMSFT-0444 1,255 Reputation points

    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 AS LoginName,   
    sp.type_desc AS LoginType,   
    'CREATE LOGIN [' + + '] FROM WINDOWS;' AS CreateLoginCommand
    FROM    sys.server_principals sp 
    sp.type IN ('S', 'U', 'G')    
    AND NOT LIKE '##%' 

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

    SELECT 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
      END + ' ' + perm.permission_name COLLATE Latin1_General_CI_AS + ' TO [' + COLLATE Latin1_General_CI_AS + '];' AS PermissionCommand
      sys.server_permissions perm
      sys.server_principals sp 
      ON perm.grantee_principal_id = sp.principal_id
      sp.type IN ('S', 'U', 'G') 
      AND NOT LIKE '##%' 

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

    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.

    0 comments No comments