Migrating Security Roles

At the Worldwide Partner Conference in Denver Microsoft revealed the next version of Microsoft CRM (Titan) will include the ability to export and import security roles.  On the surface this seems like a small change, but it was significant enough to get my attention as this has always been a sore spot for us.

True, Microsoft does provide some migration tools.  However, the simple task of deploying an implementation from a development environment to a QA or production environment usually meant the manual update of security roles -- not only was this a time consuming practice, it was also error prone.  Because of the number of records involved in the configuration of security roles and privileges and the differences of unique identifiers from one database to another there did not appear to be a simple way to script out the security configuration to automate this step in the deployment process.

Well, now there is :-)

This is something I had meant to do for a while, but never was motivated enough to sit down and work out the SQL statements.  Until today when I found myself clicking little green circles one after another applying security changes from one environment to another.  I came to a quick conclusion that I could write a script to automate this task faster than it would take me to complete the manual application of the security settings.

Warning: Unsupported actions ahead

Assumptions

1. The source and target environments have the same business unit names
2. The source and target environments have the same security role names

Instructions

Script out the source security role privileges from the source system as follows (carriage returns have been added for positing purposes):

SELECT DISTINCT 'INSERT INTO ROLEPRIVILEGES (ROLEPRIVILEGEID, ROLEID, 
PRIVILEGEID, PRIVILEGEDEPTHMASK) SELECT NEWID(), (SELECT ROLEID FROM 
ROLEBASE R INNER JOIN BUSINESSUNITBASE B ON R.BUSINESSUNITID = 
B.BUSINESSUNITID WHERE R.NAME = ''' + R.NAME + ''' AND B.NAME = '''
+ U.NAME + '''), (SELECT PRIVILEGEID FROM PRIVILEGEBASE WHERE NAME =
''' + G.NAME + '''), ' + CAST(P.PRIVILEGEDEPTHMASK AS VARCHAR(10)) +
'' SQL FROM ROLEBASE R INNER JOIN ROLEPRIVILEGES P ON R.ROLEID = 
P.ROLEID INNER JOIN BUSINESSUNITBASE U ON R.BUSINESSUNITID = 
U.BUSINESSUNITID INNER JOIN PRIVILEGEBASE G ON P.PRIVILEGEID = 
G.PRIVILEGEID WHERE R.NAME <> 'System Administrator'

OK -- I know, it's ugly.  What is it doing?  Basically, this script creates a set of insert statements to create the security role privileges in the target system.  The insert statement using the names of the business units and security roles to get their ID's in the target system.

Note, the script does not export the privileges associated with the System Administrator role as this role is constant from one installation to another.  Here is an example of the insert statement created:

INSERT INTO ROLEPRIVILEGES (
       ROLEPRIVILEGEID, 
       ROLEID, 
       PRIVILEGEID, 
       PRIVILEGEDEPTHMASK ) 
SELECT NEWID(), 
       (SELECT ROLEID 
        FROM   ROLEBASE R INNER JOIN BUSINESSUNITBASE B
                          ON R.BUSINESSUNITID = B.BUSINESSUNITID 
        WHERE  R.NAME = 'Role Name' 
        AND    B.NAME = 'Business Unit Name'),
       (SELECT PRIVILEGEID 
        FROM   PRIVILEGEBASE 
        WHERE  NAME = 'prvAppendAccount'), 
       32

As you can see the generated statement does not contain any GUID's and has the statements necessary to locate the appropriate identifiers embedded.  Just image this line repeated 1000's of times and you can start to see the value of this approach.  To run the generated statements in the target environment make sure the business units and security roles have been created (they do not need to be configured).  If you are re-applying the security privileges to an existing implementation you can clear the existing records using the following statement:

DELETE FROM  ROLEPRIVILEGES
       WHERE ROLEPRIVILEGEID NOT IN (
             SELECT     P.ROLEPRIVILEGEID 
             FROM       ROLEPRIVILEGES P 
             INNER JOIN ROLEBASE R ON P.ROLEID = R.ROLEID 
             WHERE      R.NAME = 'System Administrator')

As a reminder, making updates to the database via SQL scripts is not supported by Microsoft.  Errors in the scripts or execution of the scripts can result in damage to your Microsoft CRM installation, your data, or both.  Please proceed with caution.

How to brand your CRM implementation

Warning: unsupported (but minor) customizations ahead!

This is something we use in our development environments to quickly identify the client associate with the implementation, but is also a simple way to brand a production installation of Microsoft CRM:

To add a logo follow these steps:

1. Open the bar_top.aspx file in the <Microsoft CRM Web>\_root folder using a text editor
   
2. Replace the table with the class stdTable stageContextBar with the following:
   

   <table class="stdTable stageContextBar" cellspacing="0"
           cellpadding="0">
       <tr>
           <td class="bar">
               <nobr id="tdStageContextBar">
   <%HttpUtility.HtmlEncode(stageContextBarTitle, Response.Output);%>
               </nobr>
           </td>
           <td align="right">
               <img src="../_imgs/logo.png" />&nbsp;
           </td>
       </tr>
   </table>

3. Copy an image file to the <Microsoft CRM Web>\_imgs folder (in the example above the image is named logo.png).

If you wish you can specify the image dimensions in the HTML, as well as add a title for the mouse over.  That's it.  Nothing fancy, but it gets the job done ;-)