Home > SQL Server Security > Database user Permission and Server Role

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

Categories: SQL Server Security
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: