Database user Permission and Server Role

The script pulls user database and server level permission

Script

DECLARE @DB_USers TABLE 
(DBName sysname, UserName sysname, LoginType sysname,
AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers 
EXEC sp_MSforeachdb 
' 
use [?] 
SELECT ''?'' AS DB_Name, 
case prin.name when ''dbo'' then prin.name +

'' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'' and owner_sid!=''sa'') + '')'' else prin.name

end AS UserName, 
prin.type_desc AS LoginType, 
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date 
FROM sys.database_principals prin 
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id 
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and prin.type_desc!=''DATABASE_ROLE'' and 
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%''' 

SELECT t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user DatabaseRole, 
t2.RoleName ServerRole 
FROM (SELECT  dbname,username ,logintype ,create_date ,modify_date , 
STUFF((SELECT ',' + CONVERT(VARCHAR(500),associatedrole) 
FROM @DB_USers user2 
WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName 
FOR XML PATH('') 
),1,1,'') AS Permissions_user 
FROM @DB_USers user1) t1
left JOIN 
(SELECT Logins.name AS UserName, Roles.name AS RoleName 
    --,'EXEC sp_dropsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';', 
    --'EXEC sp_addsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';' 
FROM sys.server_role_members RoleMembers 
JOIN sys.server_principals Logins 
    ON RoleMembers.member_principal_id = Logins.principal_id 
JOIN sys.server_principals Roles 
    ON RoleMembers.role_principal_id = Roles.principal_id 
) t2 
on t1.UserName=t2.UserName   
Group by t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user, 
t2.RoleName

 

image

Leave a Reply

Your email address will not be published. Required fields are marked *