Dynamics CRM 4 – Add a Deployment administrator using SQL

A couple of days back, someone accidentally removed the sole user added to the Deployment Administrators on one of our Dynamics CRM 4 installations. There was no way that we could access the Deployment Manager console. So, we decided to look into the CRM SQL Server database for a way to add a user as a Deployment Administrator. Since, all Dynamics CRM configuration (metadata & data) is stored in the MSCRM_CONFIG and other tenant specific databases we had a hunch that it should be possible. Furthermore, since the Deployment Administrators users are common to the entire CRM deployment (as opposed to any specific tenant) we figured out that we should look only into the MSCRM_CONFIG database.

Although the investigation lasted for quite some time (with a lot of tense moments), the solution proved to be very easy & swift.

Here’s what needs to be done –

1. In Dynamics CRM, navigate to Settings -> Administration -> Users and open the user whom you want to be added to the Deployment Administrator. In the window that opens for the user, look at the URL. It should be something like this –

   http://MyCRM/MyTentant/biz/users/edit.aspx?id={A GUID Here}
   The GUID that you see in the URL is the CRMUserId for the user account. Select this GUID and copy it (Ctrl + C).

2. Open Microsoft SQL Server Management Studio, and connect to the MSCRM_CONFIG database for your CRM installation. Open a new query window, and run the following query to retrieve a few identifiers for your user account.

   SELECT Id, UserId
FROM [MSCRM_CONFIG].[dbo].[SystemUserOrganizations]
WHERE CrmUserId = 'Your CRMUserId here'

   Note down the Id and the UserId returned in this query.

3. Now, we simply need to add a row to the SystemUserRoles table for this user, using the Id and UserId returned. The User role that we need to assign so that a user can act as a Deployment Administrator is Administrator with the GUID of 19cfbc8d-77ed-459e-9909-1bf1cc1b0430. You can get this information from the SecurityRole table. Use the following query to do add the required row –

   INSERT INTO [MSCRM_CONFIG].[dbo].[SystemUserRoles]
([Id], [SecurityRoleId], [SystemUserId], [UniqueifierId], [IsDeleted])
VALUES
('Your Id returned above', '19cfbc8d-77ed-459e-9909-1bf1cc1b0430',
,'Your UserId returned above', NULL, 0)

   That’s it. This should do the trick.

   Verify the changes by opening Deployment Manager now…

New beginnings…finally…

It’s taken quite some time since I have thought about this, but finally I’ve commenced the blog.

It was never “to blog or not”, but always “where do I find the time to blog”. No matter how much I decided to put myself to work on the blog, time always found its way to other equally captivating tasks. Finally, the flurry of activities that has begun on all fronts giving me a feeling of losing track of memories & accurate records, has pushed me into committing to the blog.

Through this blog, not only I intend to keep my memories secure, but also share my experiences with you. Let’s hope this continues to be a life-long passion, just as the things that I will share are.

Happy reading…