Dynamics CRM Security Report

Microsoft Dynamics CRM has a very sophisticated security model. You can create security roles and assign permissions to entities for these roles. Then you assign security roles to teams or to a single user. All the possibilities give you a lot of power to control security permission for your users. However, it can get quite complicated to check permission for a single selected users. To do it, you must open all user’s security roles and then you have to check security roles of all user’s teams. And you have to compare all the roles and select the highest level for each access right and for each entity. This can be a time consuming task. It would be nice to have all the information available in a single report. This report could be for example used for external security audits or regular internal audit.

If you are using Dynamics CRM On-Premise, you can check the permission in Dynamics CRM database. I have created a database view which read user’s permission assigned to user and to all user’s teams. You can see the SQL code below.

SELECT BU.Name AS BusinessUnit,
	U.LastName + ' ' + U.FirstName AS FullName,
	--PRIV.[Name],
	isnull(EN.OriginalLocalizedCollectionName, EN.Name) AS EntityName,
	R.Name AS RoleName,
	CASE PRIV.AccessRight
		WHEN 1 THEN '2 Read'
		WHEN 2 THEN '3 Write'
		WHEN 4 THEN '5 Append'
		WHEN 16 THEN '6 AppendTo'
		WHEN 32 THEN '1 Create'
		WHEN 65536 THEN '4 Delete'
		WHEN 262144 THEN '7 Share'
		WHEN 524288 THEN '8 Assign'
		ELSE PRIV.Name
	END AS AccessRight,
	MAX(P.PrivilegeDepthMask) as PriviledgeDepthMask
FROM [AIMTEC_MSCRM].[dbo].[Role] R WITH (NOLOCK)
	INNER JOIN [AIMTEC_MSCRM].[dbo].[RolePrivileges] P WITH (NOLOCK)
        ON P.[RoleId]=R.ParentRootRoleId
	INNER JOIN [AIMTEC_MSCRM].[dbo].[PrivilegeObjectTypeCodes] PTOTC WITH (NOLOCK)
		ON PTOTC.PrivilegeId = P.PrivilegeId
	INNER JOIN [AIMTEC_MSCRM].[dbo].[EntityView] EN  WITH (NOLOCK)
		ON EN.ObjectTypeCode = PTOTC.ObjectTypeCode
	INNER JOIN [AIMTEC_MSCRM].[dbo].[Privilege] PRIV WITH (NOLOCK)
        ON P.[PrivilegeId]=PRIV.[PrivilegeId]
	INNER JOIN [AIMTEC_MSCRM].[dbo].[FilteredPrivilege] FPRIV WITH(NOLOCK)
		ON PRIV.PrivilegeId = FPRIV.privilegeid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[FilteredTeamRoles] TR WITH (NOLOCK)
		ON R.RoleId = TR.roleid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[Team] TEAM  WITH (NOLOCK)
		ON TEAM.TeamId = TR.teamid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[FilteredTeamMembership] TEME WITH (NOLOCK)
		ON TEAM.TeamId = TEME.teamid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[SystemUser] U WITH(NOLOCK)
		ON U.SystemUserId = TEME.systemuserid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[BusinessUnit] BU WITH(NOLOCK)
		ON BU.BusinessUnitId = U.BusinessUnitId
--WHERE R.[RoleId]=R.[ParentRootRoleId]
WHERE U.IsDisabled = 0
group by  U.LastName + ' ' + U.FirstName,
	isnull(EN.OriginalLocalizedCollectionName, EN.Name),
	CASE PRIV.AccessRight
		WHEN 1 THEN '2 Read'
		WHEN 2 THEN '3 Write'
		WHEN 4 THEN '5 Append'
		WHEN 16 THEN '6 AppendTo'
		WHEN 32 THEN '1 Create'
		WHEN 65536 THEN '4 Delete'
		WHEN 262144 THEN '7 Share'
		WHEN 524288 THEN '8 Assign'
		ELSE PRIV.Name
	END,
	BU.Name,
	R.Name

UNION ALL

SELECT BU.Name AS BusinessUnit,
	U.LastName + ' ' + U.FirstName AS FullName,
	isnull(EN.OriginalLocalizedCollectionName, EN.Name) AS EntityName,
	R.Name AS RoleName,
	CASE PRIV.AccessRight
		WHEN 1 THEN '2 Read'
		WHEN 2 THEN '3 Write'
		WHEN 4 THEN '5 Append'
		WHEN 16 THEN '6 AppendTo'
		WHEN 32 THEN '1 Create'
		WHEN 65536 THEN '4 Delete'
		WHEN 262144 THEN '7 Share'
		WHEN 524288 THEN '8 Assign'
		ELSE PRIV.Name
	END AS AccessRight,
	MAX(P.PrivilegeDepthMask) as PriviledgeDepthMask
FROM [AIMTEC_MSCRM].[dbo].[SystemUser] U WITH(NOLOCK)
	INNER JOIN [AIMTEC_MSCRM].[dbo].[FilteredSystemUserRoles] FSUR WITH(NOLOCK)
		ON U.SystemUserId = FSUR.systemuserid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[Role] R WITH (NOLOCK)
		ON R.RoleId = FSUR.roleid
	INNER JOIN [AIMTEC_MSCRM].[dbo].[RolePrivileges] P WITH (NOLOCK)
        ON P.[RoleId]=R.ParentRootRoleId
	INNER JOIN [AIMTEC_MSCRM].[dbo].[PrivilegeObjectTypeCodes] PTOTC WITH (NOLOCK)
		ON PTOTC.PrivilegeId = P.PrivilegeId
	INNER JOIN [AIMTEC_MSCRM].[dbo].[EntityView] EN  WITH (NOLOCK)
		ON EN.ObjectTypeCode = PTOTC.ObjectTypeCode
	INNER JOIN [AIMTEC_MSCRM].[dbo].[Privilege] PRIV WITH (NOLOCK)
        ON P.[PrivilegeId]=PRIV.[PrivilegeId]
	INNER JOIN [AIMTEC_MSCRM].[dbo].[BusinessUnit] BU WITH(NOLOCK)
		ON BU.BusinessUnitId = U.BusinessUnitId
WHERE U.IsDisabled = 0
group by  U.LastName + ' ' + U.FirstName,
	isnull(EN.OriginalLocalizedCollectionName, EN.Name),
	CASE PRIV.AccessRight
		WHEN 1 THEN '2 Read'
		WHEN 2 THEN '3 Write'
		WHEN 4 THEN '5 Append'
		WHEN 16 THEN '6 AppendTo'
		WHEN 32 THEN '1 Create'
		WHEN 65536 THEN '4 Delete'
		WHEN 262144 THEN '7 Share'
		WHEN 524288 THEN '8 Assign'
		ELSE PRIV.Name
	END,
	BU.Name,
	R.Name

I used Excel pivot table for the security group. I select users (grouped by business department) for a row dimension and entities for a column dimension. The values are maximums of the PriviledgeDepthMask value. Don’t forget to select the maximum function because user have the highest assigned permission. I also added conditional formatting which is consistent with CRM security settings interface.

a0003-001

Here you can see that the User 13 has higher security permissions than other users. So this user account probably belongs to some power user to department manager.

 

Trackbacks and Pingbacks

[…] is very nice and straightforward in Microsoft Dynamics CRM 2016. In a previous post I wrote about a security report which allows you to see permissions of all users at one place. However I found out one issue which […]

Like

Leave a Reply

Name and email address are required. Your email address will not be published.

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

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: