我需要让一个新的登录用户在服务器上访问所有 300 个数据库。如何在不逐一勾选用户映射区域中的 300 个复选框的情况下完成此操作？
7个回答
27
一种方法是在SSMS的查询菜单中设置“将结果设置为文本”，然后执行以下操作。
它实际上并未进行更改，而是为您生成一个脚本供您查看和执行。
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'
或者您可以查看 sys.sp_MSforeachdb ，在这里 或 Aaron Bertrand改进的版本在这里
如果您在运行此命令时未能看到所有字符，请打开文本查询选项并检查“每个列中显示的最大字符数”设置。确保该值足够大，以显示所有字符。
- Martin Smith
5
2为什么我现在才听说这个sp_msforeachdb？我猜我只是尝试用我知道的工具来解决所有问题。好吧，我会把它加入到工具箱中。谢谢！ - buckbova
谢谢！这似乎有效。我确实需要将该查询的结果复制到文本编辑器中，并用 \nGo\n 替换 Go。 - Greg
1@Greg - 很高兴它起作用了。你在管理工作室中运行它了吗？如果是这样，选择“结果为文本”选项（CTRL + T）可能会避免需要中间步骤。 - Martin Smith
默认的 SQL 文本列限制为 256，因此您需要在查询 -> 查询选项 -> 结果 -> 文本中增加此限制，以避免生成的脚本被截断。 - TheLogicMan
QUOTENAME 函数在我的 SQL2019 中无法正确处理用户名和登录名。 - Rory6
使用一些 T-SQL 光标遍历数据库并授予权限。
我没有测试下面的代码。
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
- buckbova
1
那之后要使用的T-SQL是我困惑的重点。有什么指针吗？ - Greg
5
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'
- Steven Van Epps
3
我稍微修改了Martin Smith的答案:
- 空格和换行符导致部分文本生成不正确
- 执行语句中的QUOTENAME使用了方括号，这是不正确的。
我的版本：
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'
除此之外完美运行。谢谢
- Gorata - OkomelaIT
2
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
- Thomas
2
它显示已成功完成，但我没有看到任何更改。用户映射区域没有改变。而我为<loginname>输入的登录名在任何数据库中都不会出现为用户。有什么想法吗？ - Greg
@Greg - 你可能想要注释掉Try-Catch块，以查看被抛出的错误。 - Thomas
1
我只需要一个具有数据读取权限的用户，可以访问所有数据库，所以我使用了这段代码：
顺便提一下，你需要运行查询结果。
如果您不想将其应用于系统数据库，只需添加where database_id > 6即可。
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
如果您不想将其应用于系统数据库，只需添加where database_id > 6即可。
- DnL
0
你可以使用例如游标，像这样：
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
更多信息请查看我关于此主题的帖子： http://www.pigeonsql.com/single-post/2016/12/23/Grant-User-Access-to-All-Databases
- Filip Holub
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 3 在SQL Server中创建用户、登录并授予权限
- 51 如何在SQL Server中授予用户对数据库的读取权限？
- 270 在数据库中查找所有用户的权限/访问的SQL Server查询
- 60 SQL Server授予登录用户db_owner访问数据库的权限
- 16 授予数据库完整的SQL Server权限
- 3 授予访问RDS SQL Server数据库的权限
- 12 如何为 SQL Server 用户授予 sendmail 权限？
- 114 SQL Server 2008: 如何授予用户名权限？
- 3 授予 ALTER 权限是否意味着授予对 SQL Server 存储过程的 EXECUTE 权限？
- 3 SQL Server - 查找服务器上所有数据库中用户/登录的所有权限/访问
GRANT CONNECT ANY DATABASE TO <SQL_Login>;和GRANT SELECT ALL USER SECURABLES TO <SQL_Login>;会比以前的做法更容易。请注意不要改变原文意思，并使翻译更通俗易懂。 - Dave Mason