SQL Security Roles information

Here is an SQL to retrieve all security roles privileges for all entities to help with comparing or to be used in an SSRS report

SELECT
FilteredRole.name as UserName,
EntityView.PhysicalName,
EntityView.OriginalLocalizedName as DisplayName,
Privilege.Name as PrivilegeName,
AccessLevel =
CASE Privilege.AccessRight
WHEN 1 THEN ‘READ’
WHEN 2 THEN ‘WRITE’
WHEN 4 THEN ‘APPEND’
WHEN 16 THEN ‘APPENDTO’
WHEN 32 THEN ‘CREATE’
WHEN 65536 THEN ‘DELETE’
WHEN 262144 THEN ‘SHARE’
WHEN 524288 THEN ‘ASSIGN’
END,
PrivilegeType =
CASE PrivilegeDepthMask
WHEN 1 THEN ‘Own Only’
WHEN 2 THEN ‘Business Unit’
WHEN 4 THEN ‘Parent->Child’
WHEN 8 THEN ‘Organization’
WHEN 16 THEN ‘Own Only – Inherited’
WHEN 32 THEN ‘Business Unit – Inherited’
WHEN 64 THEN ‘Parent->Child – Inherited’
WHEN 128 THEN ‘Organization – Inherited’
END
FROM
RolePrivileges inner join FilteredRole on RolePrivileges.RoleId = FilteredRole.roleid
Inner Join PrivilegeObjectTypeCodes on RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId
Inner Join Privilege on RolePrivileges.PrivilegeId = Privilege.PrivilegeId
Inner Join EntityView on EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode
WHERE FilteredRole.roletemplateid is null and OriginalLocalizedName is not null
ORDER BY FilteredRole.name, EntityView.PhysicalName, Privilege.AccessRight

Advertisements
This entry was posted in Helpers, SQL and tagged , , . Bookmark the permalink.

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