O Intuito deste T-SQL é criar usuários Read Only para todos os bancos de um determinado servidor.
Basta executar o T-SQL abaixo, pegar o output e executa-lo.
USE [master]
go
CREATE login [] WITH password=N'pass', default_database=[my_db],
check_expiration=OFF, check_policy=ON
go
SET nocount ON;
DECLARE @user_name SYSNAME,
@login_name SYSNAME;
SELECT @user_name = '<user>',
@login_name = '<user>'
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'
go
CREATE login [] WITH password=N'pass', default_database=[my_db],
check_expiration=OFF, check_policy=ON
go
SET nocount ON;
DECLARE @user_name SYSNAME,
@login_name SYSNAME;
SELECT @user_name = '<user>',
@login_name = '<user>'
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'