如何在SQL Server 2017中克隆登录及数据库用户并同步权限？

针对你遇到的问题，仅创建登录和用户映射不足以复制所有权限，需要同步角色成员身份、显式对象权限及服务器级权限，以下是可直接执行的分步脚本：

1. 创建目标服务器登录（若未创建） #

CREATE LOGIN [bcSupervisor] 
WITH PASSWORD = '设置你的密码', 
     DEFAULT_DATABASE = [StaffMonitoring], 
     CHECK_EXPIRATION = OFF, 
     CHECK_POLICY = OFF;

若为Windows域登录，替换为：CREATE LOGIN [DOMAIN\bcSupervisor] FROM WINDOWS;

2. 在目标数据库创建关联用户 #

USE [StaffMonitoring];
GO
CREATE USER [bcSupervisor] FOR LOGIN [bcSupervisor];
GO

3. 复制原用户的数据库角色成员身份 #

将原用户atSupervisor所属的所有数据库角色同步给新用户：

USE [StaffMonitoring];
GO
DECLARE @RoleName sysname;
DECLARE @SQL NVARCHAR(MAX);

DECLARE role_cursor CURSOR FOR
SELECT r.name
FROM sys.database_role_members drm
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals u ON drm.member_principal_id = u.principal_id
WHERE u.name = 'atSupervisor';

OPEN role_cursor;
FETCH NEXT FROM role_cursor INTO @RoleName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'ALTER ROLE [' + @RoleName + N'] ADD MEMBER [bcSupervisor];';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM role_cursor INTO @RoleName;
END

CLOSE role_cursor;
DEALLOCATE role_cursor;
GO

4. 复制原用户的显式对象权限（表/视图等） #

同步原用户对数据库对象的所有GRANT权限：

USE [StaffMonitoring];
GO
DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += N'GRANT ' + permission_name + N' ON [' + schema_name(o.schema_id) + N'].[' + o.name + N'] TO [bcSupervisor]' 
              + CASE WHEN state_desc = 'WITH GRANT OPTION' THEN N' WITH GRANT OPTION;' ELSE N';' END + CHAR(13) + CHAR(10)
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.database_principals u ON dp.grantee_principal_id = u.principal_id
WHERE u.name = 'atSupervisor'
AND dp.class = 1; -- 仅对象级权限（表、视图、存储过程等）

EXEC sp_executesql @SQL;
GO

5. 复制服务器级权限（若原登录有） #

同步原登录的服务器角色及显式服务器权限：

-- 复制服务器角色成员身份
DECLARE @ServerRoleName sysname;
DECLARE @ServerSQL NVARCHAR(MAX);

DECLARE server_role_cursor CURSOR FOR
SELECT sr.name
FROM sys.server_role_members srm
JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
JOIN sys.server_principals sl ON srm.member_principal_id = sl.principal_id
WHERE sl.name = 'atSupervisor';

OPEN server_role_cursor;
FETCH NEXT FROM server_role_cursor INTO @ServerRoleName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ServerSQL = N'ALTER SERVER ROLE [' + @ServerRoleName + N'] ADD MEMBER [bcSupervisor];';
    EXEC sp_executesql @ServerSQL;
    FETCH NEXT FROM server_role_cursor INTO @ServerRoleName;
END

CLOSE server_role_cursor;
DEALLOCATE server_role_cursor;
GO

-- 复制显式服务器级权限
DECLARE @ServerPermSQL NVARCHAR(MAX) = N'';

SELECT @ServerPermSQL += N'GRANT ' + permission_name + N' TO [bcSupervisor]' 
                        + CASE WHEN state_desc = 'WITH GRANT OPTION' THEN N' WITH GRANT OPTION;' ELSE N';' END + CHAR(13) + CHAR(10)
FROM sys.server_permissions sp
JOIN sys.server_principals sl ON sp.grantee_principal_id = sl.principal_id
WHERE sl.name = 'atSupervisor';

EXEC sp_executesql @ServerPermSQL;
GO

权限验证 #

可通过以下脚本测试新用户权限：

USE [StaffMonitoring];
GO
EXECUTE AS USER = 'bcSupervisor';
-- 替换为实际表名测试访问
SELECT * FROM dbo.目标表名;
-- 恢复原上下文
REVERT;
GO

内容的提问来源于stack exchange，提问作者Jafar95