SQL Server 给存储过程，视图分配查询权限（一步到位方法）

一、示例图

二、SQL代码

备注：自行替换声明变量类容即可。

-- 用户关联角色，角色关联权限
-- 声明变量
DECLARE @LoginName NVARCHAR(128) = 'user_test'; -- 登录名
DECLARE @Password NVARCHAR(128) = '123'; -- 密码
DECLARE @DefaultDB NVARCHAR(128) = 'sqldb'; -- 数据库名
DECLARE @RoleName NVARCHAR(128) = 'role_test'; -- 角色名
DECLARE @ProcedureName NVARCHAR(128) = 'pro_test'; -- 存储过程名称
-- DECLARE @ViewName NVARCHAR(128) = 'view_test'; -- 视图名称、如果需要视图权限

-- 1. 创建登录账户(如果不存在)
IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals WHERE name = @LoginName)
BEGIN
    DECLARE @CreateLoginSQL NVARCHAR(MAX) = 
    'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = ''' + @Password + ''', 
    DEFAULT_DATABASE = [' + @DefaultDB + '], 
    CHECK_EXPIRATION = OFF, 
    CHECK_POLICY = OFF;';
    
    EXEC sp_executesql @CreateLoginSQL;
    PRINT '登录账户 ' + @LoginName + ' 创建成功';
END
ELSE
BEGIN
    PRINT '登录账户 ' + @LoginName + ' 已存在';
END

-- 2. 创建数据库用户(如果不存在)
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @LoginName AND type = 'S')
BEGIN
    DECLARE @CreateUserSQL NVARCHAR(MAX) = 
    'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '];';
    
    EXEC sp_executesql @CreateUserSQL;
    PRINT '数据库用户 ' + @LoginName + ' 创建成功';
END
ELSE
BEGIN
    PRINT '数据库用户 ' + @LoginName + ' 已存在';
END

-- 3. 创建专用角色(如果不存在)
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
BEGIN
    DECLARE @CreateRoleSQL NVARCHAR(MAX) = 
    'CREATE ROLE [' + @RoleName + '];';
    
    EXEC sp_executesql @CreateRoleSQL;
    PRINT '角色 ' + @RoleName + ' 创建成功';
END
ELSE
BEGIN
    PRINT '角色 ' + @RoleName + ' 已存在';
END

-- 4. 将执行权限授予角色(而不是直接给用户)
DECLARE @GrantProcSQL NVARCHAR(MAX) = 
'GRANT EXECUTE ON ' + @ProcedureName + ' TO [' + @RoleName + '];';

EXEC sp_executesql @GrantProcSQL;
PRINT '已授予 ' + @RoleName + ' 执行 ' + @ProcedureName + ' 的权限';

-- 如果需要视图权限
-- DECLARE @GrantViewSQL NVARCHAR(MAX) = 
-- 'GRANT SELECT ON ' + @ViewName + ' TO [' + @RoleName + '];';
-- EXEC sp_executesql @GrantViewSQL;
-- PRINT '已授予 ' + @RoleName + ' 查询 ' + @ViewName + ' 的权限';

-- 5. 将用户添加到角色
DECLARE @AddRoleMemberSQL NVARCHAR(MAX) = 
'EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @LoginName + ''';';

EXEC sp_executesql @AddRoleMemberSQL;
PRINT '已将 ' + @LoginName + ' 添加到 ' + @RoleName + ' 角色';