如何将旧AD用户的全量SQL权限复制到新AD用户？

1. 提取旧用户的所有SQL权限 #

以下SQL脚本可批量导出旧用户（OLD_AD_USER）在服务器级、全数据库级及对象级的全部权限，涵盖角色成员、表/函数/存储过程等细粒度权限：

1.1 服务器级权限与角色成员

-- 导出服务器角色成员授权脚本
SELECT 
    'ALTER SERVER ROLE [' + r.name + '] ADD MEMBER [' + NEW_AD_USER + '];' AS GrantScript
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = 'OLD_AD_USER'

UNION ALL

-- 导出服务器级权限授权脚本
SELECT 
    'GRANT ' + permission_name + ' TO [' + NEW_AD_USER + '];' AS GrantScript
FROM sys.server_permissions sp
JOIN sys.server_principals spri ON sp.grantee_principal_id = spri.principal_id
WHERE spri.name = 'OLD_AD_USER'

1.2 全数据库级权限、角色成员及对象级权限

DECLARE @OldUser NVARCHAR(128) = 'OLD_AD_USER'
DECLARE @NewUser NVARCHAR(128) = 'NEW_AD_USER'
DECLARE @SQL NVARCHAR(MAX) = ''

-- 遍历所有在线数据库生成权限脚本
SELECT @SQL = @SQL + '
USE [' + name + '];
-- 数据库角色成员
SELECT 
    ''USE [' + name + ']; ALTER ROLE [' + r.name + '] ADD MEMBER [' + @NewUser + '];'' AS GrantScript
FROM [' + name + '].sys.database_role_members rm
JOIN [' + name + '].sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN [' + name + '].sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = ''' + @OldUser + '''

UNION ALL

-- 数据库级权限
SELECT 
    ''USE [' + name + ']; GRANT ' + permission_name + ' TO [' + @NewUser + '];'' AS GrantScript
FROM [' + name + '].sys.database_permissions dp
JOIN [' + name + '].sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE dpri.name = ''' + @OldUser + '''

UNION ALL

-- 对象级权限（表、视图、函数、存储过程等）
SELECT 
    ''USE [' + name + ']; GRANT ' + permission_name + ' ON [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] TO [' + @NewUser + '];'' AS GrantScript
FROM [' + name + '].sys.database_permissions dp
JOIN [' + name + '].sys.objects o ON dp.major_id = o.object_id
JOIN [' + name + '].sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE dpri.name = ''' + @OldUser + ''' AND o.type IN (''U'', ''V'', ''FN'', ''IF'', ''TF'', ''P'', ''PC'')
'
FROM sys.databases WHERE state = 0

EXEC sp_executesql @SQL

2. 生成并执行授权脚本 #

1. 将上述脚本中的OLD_AD_USER替换为旧AD用户名（格式：DOMAIN\OldUserName），NEW_AD_USER替换为新AD用户名（格式：DOMAIN\NewUserName）
2. 执行脚本，复制结果集中的GrantScript列内容，形成完整的授权脚本
3. 在SQL Server中执行该授权脚本，完成权限同步

3. 验证权限一致性 #

执行以下脚本对比新旧用户权限，确保无遗漏：

-- 对比服务器级权限差异
SELECT permission_name FROM sys.server_permissions sp
JOIN sys.server_principals spri ON sp.grantee_principal_id = spri.principal_id
WHERE spri.name = 'OLD_AD_USER'
EXCEPT
SELECT permission_name FROM sys.server_permissions sp
JOIN sys.server_principals spri ON sp.grantee_principal_id = spri.principal_id
WHERE spri.name = 'NEW_AD_USER'

-- 对比全数据库级权限差异
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = @SQL + '
USE [' + name + '];
SELECT ''' + name + ''' AS DatabaseName, permission_name FROM [' + name + '].sys.database_permissions dp
JOIN [' + name + '].sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE dpri.name = ''OLD_AD_USER''
EXCEPT
SELECT ''' + name + ''' AS DatabaseName, permission_name FROM [' + name + '].sys.database_permissions dp
JOIN [' + name + '].sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE dpri.name = ''NEW_AD_USER''
'
FROM sys.databases WHERE state = 0

EXEC sp_executesql @SQL

若结果集为空，说明权限已完全同步；若有返回结果，针对遗漏项补充授权即可。

内容的提问来源于stack exchange，提问作者P. K.