How to transfer logins and passwords between instances of SQL Server
How to transfer logins and passwords between instances of SQL Server
And if we want to migrate server level role permissions too then we can use below script.
SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa');
SELECT
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN SrvPerm.state_desc
ELSE 'GRANT'
END
+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN ''
ELSE ' WITH GRANT OPTION'
END collate database_default AS [-- Server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' )
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');
And if we want to migrate server level role permissions too then we can use below script.
SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa');
SELECT
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN SrvPerm.state_desc
ELSE 'GRANT'
END
+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN ''
ELSE ' WITH GRANT OPTION'
END collate database_default AS [-- Server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' )
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');
Comments