List privileges for security roles in Microsoft Dynamics CRM 2011/2013

So a couple of weeks back I was asked to disable export to excel functionality for all users, unfortunately nobody was sure which roles had the Export To Excel privilege (prvExportToExcel) so I used this query to find out:

select r.name as Role, x.label,p.Name,
case p.accessright -- technically a bit field, but CRM treats as an int
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'
else '?' end as Permission,

case rp.PrivilegeDepthMask -- technically a bit field, but CRM treats as an int
when 1 then 'User'
when 2 then 'BusinessUnit'
when 4 then 'Parent:Child'
when 8 then 'Organisation'
else '?' end as Scope
from role r

left join RolePrivileges rp on r.roleid = rp.roleid
left join Privilege p on rp.privilegeid = p.privilegeid
left join PrivilegeObjectTypeCodes potc on p.privilegeid = potc.privilegeid
left join MetadataSchema.Entity e on potc.objecttypecode = e.objecttypecode
left join (select max(overwritetime) as owt, label, localizedlabelid, objectid, objectcolumnname from 

MetadataSchema.LocalizedLabel group by localizedlabelid, label, objectid, objectcolumnname) as x on e.entityid= x.objectid and x.objectcolumnname = 'LocalizedName'

where p.name like 'prvExportToExcel'
order by r.name, e.logicalname, 3, 4

The left joins are necessary to ensure that the On/Off privileges like Export To Excel are included. This was the result.

Getting over the 8192 SQL Server truncation limit in SSMS

Today, I was trying to find out whether any workflow was modifying an entity as my memory was somewhat hazy and since I was already logged on the database server, I thought I'd check on the database server, but alas I couldn't as the results were being truncated, which made the query useless.

I whinged a bit to a colleague who simply said:

Why don't you cast the xaml to xml?

My response was, I didn't know that casting the xaml to xml would help, but it does.

select top 1 name, cast (xaml as xml) from workflow
where xaml like '%Process%'

This is the result:

Clicking on the xaml will open a the workflow definition on different window as an xml document.

Business Closures in MS Dynamics CRM

We had an incident last week, where a user complained of not being able to create business closures, specifically of a business closure for Christmas. When we had a look at the system, the business closure was there. Since there was a bit of lag between the incident being raised and it getting to us, we simply closed it assuming that it was a temporary glitch, the eternal hope of the support professional (I use professional in the loosest sense of the word), alas it wasn’t.

The incident came back and this is when we realized that there is no way of telling when or by whom had a business closure been created. A scan of the database tables did not help and then I had a flash of inspiration so looked at the calendarbase table and sure enough there is a calendar whose name is 'Business Closure Calendar' and each business closure is a calendarrule linked to this calendar, which means that you can use this query to retrieve any information about all business closures:

Select * from calendarrule where calendarid in (select calendarid from calendar where name='Business Closure Calendar')

This allowed us to discover a design fault in the security roles that allows one type of users to create them but crucially not the right type, god knows how it was missed during system testing.

I just had a look at one of our development boxes and this also applies to MS Dynamics CRM 2011.

Find duplicate rows in a SQL Server database

From time to time I've found myself going through the database trying to find duplicates of this or the other and because this does not happen often enough I forget how to do it. So I was at it today again and ran into the same hole in my memory, so in order to never forget again, as if, I've decided to document it here, so here we go:

select callid,count(callid) as numberofcalls from calls
group by callid
having ( count(callid) > 1 )

This will display the callid and the number of times it appears on the calls table, provided that callid appears more than once.

If you are wondering why are there duplicate callids, so am I. Oh, the joys of IT support.

SQL Self-Joins

I was trying to simplify our troubleshooting efforts when dealing with a certain type of issue in one of our systems, by using a sql query, and properly apportion blame, i.e. tell the customer it’s their fault, when I hit an issue. My query was misbehaving, it was not working properly, returning twice some values, but others were ok.

After a bit of thinking, I realized that what I needed was a way of using two join conditions. I wasn’t sure whether this was possible, but it turns out that it is indeed possible and very simple.

All that is needed is this, in bold, full query for reference:

Select t1.id, t1.session, t1.date from

(select id, session, date from mytable where ….)t1

Join

(select count(*) as items, idname, scheduledsession from myothertable

Group by idname, scheduledsession)t2

on (t1.id = t2.id and t1.session = t2.session)

order by t1.id desc