如何创建SQL Server数据库角色以访问所有现有及未来视图？

1. 创建自定义只读视图角色 #

先创建一个专门用于访问视图的自定义角色：

CREATE ROLE ViewOnlyReader;

2. 批量授予现有视图的SELECT权限 #

通过系统视图遍历所有现有视图，批量给角色授权：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'GRANT SELECT ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N' TO ViewOnlyReader;' + CHAR(13) + CHAR(10)
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id;

EXEC sp_executesql @sql;

3. 自动获取未来新建视图的权限 #

方案一：架构级授权（推荐，适配后续架构化需求） #

对数据库内所有现有架构授予SELECT权限，后续该架构下新建的视图会自动继承权限：

DECLARE @schemaSql NVARCHAR(MAX) = N'';

SELECT @schemaSql += N'GRANT SELECT ON SCHEMA::' + QUOTENAME(s.name) + N' TO ViewOnlyReader;' + CHAR(13) + CHAR(10)
FROM sys.schemas s;

EXEC sp_executesql @schemaSql;

方案二：DDL触发器精准控制 #

如果需要仅针对视图做授权（不包含架构下其他对象），可以创建DDL触发器，在新视图创建时自动给角色加权限：

CREATE TRIGGER GrantViewPermissionsOnCreate
ON DATABASE
FOR CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @eventData XML = EVENTDATA();
    DECLARE @schemaName NVARCHAR(128) = @eventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)');
    DECLARE @viewName NVARCHAR(128) = @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
    
    DECLARE @grantSql NVARCHAR(MAX) = N'GRANT SELECT ON ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@viewName) + N' TO ViewOnlyReader;';
    EXEC sp_executesql @grantSql;
END;

4. 添加用户到角色 #

将需要访问视图的用户加入该自定义角色：

ALTER ROLE ViewOnlyReader ADD MEMBER [你的用户名];

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