How to get security privileges in excel format in MS CRM

How to get security privileges in excel format in MS CRM using sql

Ms CRM sql RolePrivileges  table

Sometimes we need to get what all privileges a security role have, quickly without going into CRM standard security roles and find out.
Or if we want to keep the copy of privileges to different security roles as a reference to be used at some point of time in future, we have following sql query running which in sql server will give you list of name of security role, entity name with access level and security level:

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SELECT DISTINCT FilteredRole.name, EntityView.PhysicalName AS [Entity Name], 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 AS [Access Level], CASE PrivilegeDepthMask WHEN 1 THEN 'User' WHEN 2 THEN 'Business Unit' WHEN 4 THEN 'Parent: Child Business Unit' WHEN 8 THEN 'Organisation' END AS [Security Level] 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 ORDER BY FilteredRole.name, [Entity Name]


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Hope the results are helpful..!! :)

Happy CRMing..!!

How to find workflows running on the user context in MS CRM

How to find workflows running on the user context in MS CRM using sql

Sql query to get all workflows which are running on user's context, workflowbase table in ms crm

Recently i needed to find out all the workflows which are running in the context of  the user who made changes to the record. To quickly find out the same we need to query workflowbase

 table in crm db.

Below is the query.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Select 

distinct(W.name)

 from workflowbase as W

join SystemUser  as S

on W.OwnerId=S.SystemUserId

where S.FullName = 'CRM Admin'

and W.IsCrmUIWorkflow=1 --

and W.RunAs=1 --1 the user who made changes to the record 0-owner of the workflow

and W.ParentWorkflowId is   null --For workflow header rows only

and mode=0 --for real time workflow only 0- real time 1-background

and w.Category=0 --Category 2- Business rule and 0 is workflow

order by W.name

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Happy CRMing

Dynamics CRM How to view records shared with a user

Dynamics crm How to see records shared with current user

In Dynamics crm you can create different types of views but by oob views it's not possible to show the records shared with current user in any view.

For achieving we need to update the xml of a system view to make it show shared records.

Scenario: Let's say we have a view of lead named "my shared leads".

Following is the fetch xml of the view which can be downloaded from advanced find.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="lead">
    <attribute name="fullname" />
    <attribute name="companyname" />
    <attribute name="telephone1" />
    <attribute name="leadid" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Now to make it show shred records we have to update it's fetch xml with following xml:
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

<link-entity name='principalobjectaccess' to='leadid' from='objectid' link-type='inner' alias='share'>
 <filter type='and'>
 <condition attribute='principalid' operator='eq-userid' />
 </filter>
</link-entity>
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

principalobjectaccess table store shared records.

to: primary field of the entity for which the view is (leadid in our case)

condition: principalid

Operator: eq-userid means princinpal user should be equal to current user.

Updated XML will look like

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="lead">

    <attribute name="fullname" />

    <attribute name="companyname" />

    <attribute name="telephone1" />

    <attribute name="leadid" />

    <order attribute="fullname" descending="false" />

<link-entity name='principalobjectaccess' to='leadid' from='objectid' link-type='inner' alias='share'>

 <filter type='and'>

 <condition attribute='principalid' operator='eq-userid' />

 </filter>

</link-entity>

  </entity>

</fetch>

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Now get the guid of view from database or by querying as follows

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   public Guid  GetSavedQueryId(IOrganizationService service,String viewname)

        {

            Guid result = Guid.Empty;

            QueryExpression q = new QueryExpression("savedquery");

            q.Criteria.AddCondition("name", ConditionOperator.Equal, viewname);

            EntityCollection en = service.RetrieveMultiple(q);

            if (en.Entities.Count > 0)

                result= en.Entities[0].Id;

            return result;

            

        }

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Once you got the Guid of the view you can update it with the help of following code:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

public void UpdateSavedQuery(IOrganizationService service,Guid viewid)
 {
 Entity UpdateQuery = new Entity("savedquery");
 UpdateQuery.Id = viewid;  //new Guid("12bc926f-26c0-e111-a4f9-00155d1c5b01"); //Guid of the view to be updated
 UpdateQuery["fetchxml"] = @" <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='lead'>
<attribute name='fullname' />
<attribute name='companyname' />
<attribute name='telephone1' />
<attribute name='leadid' />
<order attribute='fullname' descending='false' />
<link-entity name='principalobjectaccess' to='leadid' from='objectid' link-type='inner' alias='share'>
<filter type='and'>
<condition attribute='principalid' operator='eq-userid' />
</filter>
</link-entity>
  </entity>
</fetch>";

service.Update(UpdateQuery);
 }
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Don't forget to replace " in fetch xml with ' .

Once you execute above code, you'll be able to see shared record in your view.

Happy CRM