Мне нужно предоставить новый доступ для входа во все 300 баз данных на сервере. Как я могу выполнить это без проверки 300 флажков в области сопоставления пользователя?
SQL Server - как предоставить доступ для чтения ко всем базам данных для входа?
Ответ 1
Один из способов - установить "Результаты в текст" в меню запросов в SSMS, а затем выполнить ниже.
На самом деле это не изменение, но генерирует script для просмотра и выполнения.
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 улучшенная версия здесь
Если вы не видите все символы при запуске, откройте "Параметры запроса для текста" и проверьте параметр "Максимальное количество символов, отображаемых в каждом столбце". Убедитесь, что для этого параметра установлено значение, достаточное для отображения всех символов.
Ответ 2
Курсор через базы данных и доступ GRANT к каждому с небольшим 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
Ответ 3
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'
Ответ 4
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
Ответ 5
Мне пришлось слегка подправить Мартина Смита, как:
- Пробелы и разрывы строк привели к тому, что не все тексты были правильно созданы
- QUOTENAME в выражении Exec помещается в квадратные скобки, что неверно.
Моя версия:
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'
В противном случае отлично работает. Благодаря
Ответ 6
Мне просто нужен был пользователь, который будет иметь доступ ко всей базе данных с разрешением на чтение данных, поэтому я использовал этот код: вам нужно будет запустить результат из запроса 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
если вы не хотите применять его в системных базах данных, просто добавьте где database_id > 6
Ответ 7
Вы можете использовать, например, Курсор, например:
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