如何为数据库中新建用户B复制用户A的表和视图权限？

故事背景：

公司使用的是SQL Server数据库，经常会碰到一种情况，需要为新入职的员工赋予同组内其他同事的权限。

    常用方法：

• 1，为同一组申请创建统一的Security Group(安全组)，为创建的组分配相关表和视图的访问权限。不管员工入职还是离职，仅需将组内的成员进行相关的添加和删除即可。
• 2，由于某些原因，如数据权限最小原则，员工A仅有数据库中某几张表和视图的查询权限。当员工A出现职位变动时，需要为接替者员工B赋予员工A的数据库访问权限。如果逐一给员工B添加相关表和视图的查询权限，则过程漫长，还有可能造成权限遗漏。

---

 

当前篇章我们主要详解第2种情况，应该怎样快速把数据库中员工A表和视图的查询权限快速赋予员工B呢？

解决方案：

1，在数据库中创建用户B（如果用户B的登录已存在，则只需创建映射用户B到该登录的数据库）：

USE [ABC]; -- 切换到目标数据库
GO

-- 假设B是数据库用户，不是登录名
CREATE USER [B] FOR LOGIN [B]; -- 如果B是Windows登录或其他类型登录，需要相应地更改
GO

2，将用户A表查询权限赋予用户B：

由于直接复制权限比较复杂，需要遍历所有表和视图，并且检查用户A被授予的SELECT权限。然后再把查到的用户A的表和视图权限赋予用户B。

以下是如何为用户B授予用户A的SELECT权限的示例代码：

USE [ABC]; -- 确保使用正确的数据库
GO

DECLARE @TableName NVARCHAR(256);
DECLARE @SQL NVARCHAR(MAX) = '';

-- 获取用户A在dbo架构下所有表和视图的SELECT权限，并构建授权语句
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(t.name)
FROM sys.tables AS t
JOIN sys.database_permissions AS dp ON t.object_id = dp.major_id
WHERE dp.permission_name = 'SELECT' AND dp.grantee_principal_id = USER_ID('A') AND dp.class = 1

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = @SQL + 'GRANT SELECT ON ' + @TableName + ' TO [B];' + CHAR(13);
    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- 执行构建的授权语句
EXEC sp_executesql @SQL;
GO

-- 同上，为视图赋予权限
DECLARE ViewCursor CURSOR FOR
SELECT QUOTENAME(v.name)
FROM sys.views AS v
JOIN sys.database_permissions AS dp ON v.object_id = dp.major_id
WHERE dp.permission_name = 'SELECT' AND dp.grantee_principal_id = USER_ID('A') AND dp.class = 0

OPEN ViewCursor;
FETCH NEXT FROM ViewCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = @SQL + 'GRANT SELECT ON ' + @TableName + ' TO [B];' + CHAR(13);
    FETCH NEXT FROM ViewCursor INTO @TableName;
END

CLOSE ViewCursor;
DEALLOCATE ViewCursor;

EXEC sp_executesql @SQL;
GO

 

请注意，上述脚本使用了动态SQL来构建权限授予的语句，并对每个表和视图执行了授权。QUOTENAME函数用于确保表名和视图名被正确地引用，防止SQL注入。

此外，USER_ID('A')函数获取用户A的数据库主ID，dp.class = 1表示表（OBJECT），dp.class = 0表示视图（VIEW）。在实际应用中，你可能需要根据实际情况调整上述脚本，以确保正确地复制所需的权限。

在执行这些操作之前，请确保你具有足够的权限，并且了解这些操作的后果，因为它们可能会对数据库的安全性和访问控制产生重大影响。

今天分享就到这，想了解更多小技巧，记得关注我哦！