Script to find database level roles assigned to database level users


Below script give the list of  database level roles assigned to database level users


/******************************************************/
DECLARE @SQLStatement VARCHAR(4000)
DECLARE @T_DBuser TABLE
(DBName SYSNAME, UserName SYSNAME, AssociatedDBRole NVARCHAR(256),
Type_d VARCHAR(100), Schema_N VARCHAR(100))
SET @SQLStatement='
SELECT ''[?]'' AS DBName,dp.name AS UserName,
USER_NAME(drm.role_principal_id) AS AssociatedDBRole,
type_desc as Type_d ,default_schema_name as Schema_N
FROM [?].sys.database_principals dp
LEFT OUTER JOIN [?].sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'')
AND dp.is_fixed_role <>1 AND dp.name NOT LIKE ''##%''
AND ''[?]'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT * FROM @T_DBuser ORDER BY DBName

/***************************************************



No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services