如何为SQL Server所有数据库授予用户SELECT权限？

方法1：动态SQL批量授权现有数据库 #

如果你需要给现有所有用户数据库的dbo schema授予SELECT权限（带GRANT OPTION），可以用动态SQL遍历所有非系统数据库，自动生成并执行授权语句：

DECLARE @SQL NVARCHAR(MAX) = N''

SELECT @SQL += N'
USE [' + name + N'];
GRANT SELECT ON SCHEMA::dbo TO USER1 WITH GRANT OPTION;
'
FROM sys.databases
WHERE database_id > 4 -- 排除master、model、msdb、tempdb系统库
  AND state = 0 -- 只包含在线的数据库

EXEC sp_executesql @SQL

执行前确保你有足够权限，比如CONTROL SERVER或每个数据库的ALTER ANY USER权限。

方法2：服务器级别权限（谨慎使用） #

如果需要让用户拥有几乎所有数据库的查询权限，可以直接授予服务器级别的宽泛权限，但注意这会赋予用户访问所有用户可访问对象的权限，范围极大：

GRANT SELECT ALL USER SECURABLES TO USER1;
GRANT VIEW ANY DEFINITION TO USER1;

这种方法无需逐个数据库操作，但权限过大，仅在确定需要时使用。

方法3：自动授权新建数据库（可选） #

如果希望未来新建的数据库也自动给该用户授权，可以创建服务器级触发器，在新数据库创建时自动执行授权：

CREATE TRIGGER AutoGrantUserPermissions
ON ALL SERVER 
FOR CREATE_DATABASE
AS
BEGIN
    DECLARE @DBName NVARCHAR(128) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
    DECLARE @SQL NVARCHAR(MAX) = N'
    USE [' + @DBName + N'];
    GRANT SELECT ON SCHEMA::dbo TO USER1 WITH GRANT OPTION;
    '
    EXEC sp_executesql @SQL
END

注意事项 #

• 执行授权的账号必须具备对应权限，比如服务器级CONTROL SERVER，或目标数据库的GRANT权限。
• 系统数据库通常不需要给普通用户授权，脚本默认排除，若需包含可去掉database_id > 4的条件。
• WITH GRANT OPTION允许该用户将权限转授他人，不需要可直接删除这部分。

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