Home | Contact Us

Powered by Blogger

Friday, July 20, 2007

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.

Labels: , ,

Links to this post:

<< Home
Copyright © 2007 AdvantageWorks Software Group, LLC. All rights reserved.