How to map a login to all the user databases with relevant DB Roles & cross verify the permissions

Hi Mates,

This is perhaps one of the common scenarios where we need to map a login to all the user databases with relevant database roles (say in my case db_datareader) & cross check whether they got applied on all the databases with the expected permissions.

Below is the script to map a login on all the user databases at single shot

USE [master]
EXEC sp_MSforeachdb N'
IF N''?'' NOT IN(SELECT name FROM sys.sysdatabases where dbid<=4)
BEGIN
USE [?]
CREATE USER [test] FOR LOGIN [test]
EXEC sp_addrolemember db_datareader, [test]
END;
';

Next script will help you to identify if the permissions were in place for the requested login.

DECLARE @name sysname,
@sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint

IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable

CREATE TABLE #tmpTable
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)

DECLARE c1 CURSOR for
SELECT name FROM master.sys.databases

OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name
FROM [' + @name + '].sys.database_principals a
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name = ''test'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1

SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)
FROM #tmpTable

SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)
FROM #tmpTable

SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)
FROM #tmpTable

SET @sql = 'SELECT LEFT(DBName, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'LEFT(UserName, ' + LTRIM(STR(@maxlen2)) + ') AS ''User Name'', '
SET @sql = @sql + 'LEFT(RoleName, ' + LTRIM(STR(@maxlen3)) + ') AS ''Role Name'' '
SET @sql = @sql + 'FROM #tmpTable '
SET @sql = @sql + 'ORDER BY DBName, UserName'
EXEC(@sql)