SQL Server - Как Предоставить Доступ для чтения ко ВСЕМ базам данных к Входу в систему?
Я должен дать новый доступ для чтения входа в систему ко всем 300 базам данных по серверу. Как я могу выполнить это, не проверяя 300 флажков в пользовательскую область отображения?
3 ответа
Один из способов - установить «Результаты в текст» в меню запроса в 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 как здесь или на улучшенную версию Аарона Бертрана здесь
Если вы не видите все символы при запуске, откройте Параметры запроса для текста и проверьте настройку «Максимальное количество символов, отображаемых в каждом столбце». Убедитесь, что это значение достаточно велико для отображения всех символов.
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
Пройдитесь по базам данных и дайте доступ к каждой из них с помощью 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