SQL Server - 如何向登录名授予对所有数据库的读取访问权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3087140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - How to Grant Read Access to ALL databases to a Login?
提问by Greg
I need to give a new login read access to all 300 databases on a server. How can I accomplish this without checking 300 checkboxes in the user mapping area?
我需要授予对服务器上所有 300 个数据库的新登录读取访问权限。如何在不选中用户映射区域中的 300 个复选框的情况下完成此操作？
回答by Martin Smith
One way would be to Set "Results to Text" on the query menu in SSMS then execute the below.
一种方法是在 SSMS 的查询菜单上设置“结果为文本”，然后执行以下操作。
It doesn't actually make the change but generates a script for you to review and execute.
它实际上并没有进行更改，而是生成一个脚本供您查看和执行。
SET NOCOUNT ON;
DECLARE @user_name SYSNAME
, @login_name SYSNAME;
SELECT @user_name = 'user_name',
@login_name = 'login_name'
SELECT '
USE ' + QUOTENAME(NAME) + ';
CREATE USER ' + QUOTENAME(@user_name)
+ ' FOR LOGIN ' + QUOTENAME(@login_name)
+ ' WITH DEFAULT_SCHEMA=[dbo];
EXEC sys.sp_addrolemember
''db_datareader'',
''' + QUOTENAME(@user_name) + ''';
EXEC sys.sp_addrolemember
''db_denydatawriter'',
'''
+ QUOTENAME(@user_name) + ''';
GO
'
FROM sys.databases
WHERE database_id > 4
AND state_desc = 'ONLINE'
Or you could look at sys.sp_MSforeachdbas hereor Aaron Bertrand's improved version here
或者，你可以看看sys.sp_MSforeachdb这里或亚伦Bertrand的改进版本在这里
If you are not seeing all of the characters when you run this, open the Query Options for Text and check the setting for 'Maximum number of characters displayed in each column'. Make sure this is set to a value large enough to display all characters.
如果运行此命令时没有看到所有字符，请打开文本查询选项并检查“每列中显示的最大字符数”的设置。确保将其设置为足够大的值以显示所有字符。
回答by buckbova
Cursor through the databases and GRANT access to each with a little t-sql.
游标通过数据库并使用一点 t-sql 授予对每个数据库的访问权限。
I did not test the code below.
我没有测试下面的代码。
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
WHILE @@FETCH_STATUS = 0
BEGIN
GRANT SELECT ON DATABASE::@name to 'username';
FETCH NEXT FROM db_cursor INTO @name
END
回答by Steven Van Epps
EXEC sp_MSForEachDB
'Declare @name varchar(100)
select @name = ''?''
PRINT @name
IF db_id(@name) > 4
BEGIN
USE ?
CREATE USER [user] FOR LOGIN [user];
EXEC sp_addrolemember ''db_datareader'', ''user''
END'
回答by Gorata - OkomelaIT
I had to tweak Martin Smith's answer slightly as:
我不得不稍微调整一下马丁史密斯的回答：
- The spaces and linebreaks resulted in not all the text being generated properly
- The QUOTENAME in the Exec statement put in square brackets which is incorrect.
- 空格和换行符导致并非所有文本都正确生成
- Exec 语句中的 QUOTENAME 放在方括号中，这是不正确的。
My version:
我的版本：
SET NOCOUNT ON;
DECLARE @user_name SYSNAME
, @login_name SYSNAME;
SELECT @user_name = 'HelpdeskUser',
@login_name = 'Helpdesk'
SELECT 'USE ' + QUOTENAME(NAME) + ';
CREATE USER ' + QUOTENAME(@user_name)
+ ' FOR LOGIN ' + QUOTENAME(@login_name)
+ ' WITH DEFAULT_SCHEMA=[dbo];
EXEC sys.sp_addrolemember ''db_datareader'',''' + @user_name + ''';
EXEC sys.sp_addrolemember ''db_denydatawriter'', ''' + @user_name + ''';
GO'
FROM sys.databases
WHERE database_id > 4
AND state_desc = 'ONLINE'
Otherwise works perfectly. Thanks
否则工作完美。谢谢
回答by Thomas
Declare @Databases Cursor
Declare @DbName as nvarchar(64)
Declare @Sql nvarchar(max)
Declare @BaseAddUserSql nvarchar(max)
Declare @BaseAddRoleSql nvarchar(max)
Set @Databases = Cursor Fast_Forward For
select [name]
from master..sysdatabases
where [name] not in('master','model','msdb','tempdb')
Open @Databases
Fetch Next From @Databases Into @DbName
Set @BaseAddUserSql = 'exec sp_adduser ''LOGINNAME'''
Set @BaseAddRoleSql = 'exec sp_addrolemember ''db_datareader'', ''LOGINNAME'''
While @@Fetch_Status = 0
Begin
Begin Try
Set @Sql = 'Use ' + Quotename(@DbName)
exec (@Sql)
Set @Sql = Replace(@BaseAddUserSql, 'LOGINNAME', <loginname>)
exec(@Sql)
Set @Sql = Replace(@BaseAddRoleSql, 'LOGINNAME', <loginname>)
exec(@Sql)
End Try
Begin Catch
End Catch
Fetch Next From @Databases Into @DbName
End
Close @Databases
Deallocate @Databases
回答by DnL
I just needed a user that will have access to all database with a data reader permission so i used this code: you will need to run the result from the query btw.
我只需要一个可以访问所有具有数据读取器权限的数据库的用户，所以我使用了这段代码：顺便说一句，您需要运行查询的结果。
USE [master]
GO CREATE LOGIN [DOMAIN\USER] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO
select 'use ['+name+']
CREATE USER [DOMAIN\USER] FOR LOGIN [DOMAIN\USER]
EXEC sp_addrolemember N''db_datareader'', N''DOMAIN\USER''
'
from sys.databases
if you don't want to apply it on system databases just add where database_id > 6
如果您不想将其应用于系统数据库，只需添加 where database_id > 6
回答by Filip Holub
You can use for example Cursor, like this :
例如，您可以使用Cursor，如下所示：
USE master
GO
DECLARE @DatabaseName VARCHAR(32)
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[SQL\srvSSISAcc]' --Your User
DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN Grant_Permission
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User +
'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'',
' + @User + '; EXEC sp_addrolemember N''db_datawriter'', ' + @User + ''
EXEC sp_executesql @SQL
PRINT @SQL
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
END
CLOSE Grant_Permission
DEALLOCATE Grant_Permission
More info find in my post about this topic: http://www.pigeonsql.com/single-post/2016/12/23/Grant-User-Access-to-All-Databases
有关此主题的更多信息可在我的帖子中找到：http: //www.pigeonsql.com/single-post/2016/12/23/Grant-User-Access-to-All-Databases
