Database roles + permissions are stored in the database itself, no need to transfer anything beside the databases.
Or what for permissions do you mean?
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
By using sp_help_revlogin, we can transfer the logins and passwords to the secondary replica. How to transfer roles and permissions?
Database roles + permissions are stored in the database itself, no need to transfer anything beside the databases.
Or what for permissions do you mean?
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.