----------------------To grant db_reader permission to all user for particular DB by bulk
DECLARE @name VARCHAR(50) -- database name
DECLARE @user sysname
DECLARE @login sysname
DECLARE @SQL nvarchar(max) = ''
DECLARE db_cursor CURSOR READ_ONLY FOR
select name from sys.syslogins where name not in ('sa','dbadmin') and name not like '%#%' and isntname=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @login=@name
SET @user=@name
SELECT @SQL = @SQL + '
USE ' + QUOTENAME(NAME) + ';
IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
BEGIN
CREATE USER ' + QUOTENAME(@user)
+ ' FOR LOGIN ' + QUOTENAME(@login)
+ ' WITH DEFAULT_SCHEMA=[dbo];
END
ELSE
BEGIN
EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
END
EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';
'
FROM sys.databases
WHERE database_id > 4 AND state = 0 and database_id=15
EXEC sp_executesql @sql;
print @sql
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
----------------------To grant db_reader permission particular user for particular /all DB
DECLARE @user sysname = 'testpub123';
DECLARE @login sysname = 'testpub123';
DECLARE @SQL nvarchar(max) = '';
SELECT @SQL = @SQL + '
USE ' + QUOTENAME(NAME) + ';
IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
BEGIN
CREATE USER ' + QUOTENAME(@user)
+ ' FOR LOGIN ' + QUOTENAME(@login)
+ ' WITH DEFAULT_SCHEMA=[dbo];
END
ELSE
BEGIN
EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
END
EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';
'
FROM sys.databases
WHERE database_id > 4 AND state = 0 and database_id=24;
EXEC sp_executesql @sql;
print @sql
--select * from sys.sysusers
--sp_helpdb Demo_DB1
--select * from sys.databases
