Transfer roles and permissions

Chaitanya Kiran 776 Reputation points
2024-05-27T13:17:28.1966667+00:00

Hi,

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
    2024-05-27T16:54:05.7533333+00:00

    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
    2024-05-28T09:31:52.3433333+00:00

    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.

    0 comments No comments