How to assign System Administrator role for a user in CRM using SQL?

Recently in Dynamics 365 On-premise Dev environment, accidentally System Admin user’s Business Unit changed and hence all the security roles lost along with System Administrator role.

In that Dev environment, we don’t have any other user with System Administrator security role.

So, followed the below steps and assigned the System Administrator role to the CRM System Admin user using SQL queries.

Note: Take the backup of CRM DB before following the below steps.

Step 1: Logged in SQL with CRM System Admin role and executed the below query and got the Default business unit GUID from the BusinessUnit Table.

SELECT
BUSINESSUNITID,
NAME,*
FROM
BUSINESSUNIT
WHERE
PARENTBUSINESSUNITID IS NULL


Step 2: Executed the below query and got the CRM System Admin User GUID from the SystemUser table.

SELECT
FULLNAME,
SYSTEMUSERID,
BUSINESSUNITID,
BUSINESSUNITIDNAME,*
FROM
FILTEREDSYSTEMUSER
WHERE
FULLNAME LIKE ‘%CRM ADMIN%’ — CRM ADMIN is the name of my System Admin’s Full name in the CRM dev organization.

Step 3: Updated the default business unit GUID for the System Admin user by using the below SQL Query.

UPDATE
FILTEREDSYSTEMUSER
SET
BUSINESSUNITID = ‘6C9A857D-D43B-E411-93F9-000D3A800C9F’ — Refer Step 1.
WHERE SYSTEMUSERID=’F2368783-D43B-E411-93F9-000D3A800C9F’ — Refer Step 2.

Step 4: Executed the below query and got the below System Administrator Security role from Role Table.

SELECT
ROLEID,
NAME,
BUSINESSUNITID,*
FROM
ROLE
WHERE
NAME = ‘SYSTEM ADMINISTRATOR’ AND
CREATEDBY IS NULL

Step 5: Inserted the record in SystemUserRoles Table.

INSERT
INTO
SYSTEMUSERROLES (SYSTEMUSERID, ROLEID)
VALUES
(‘547258C8-3265-E911-A816-000D3A81C32B’,’1E4B4AB7-C956-E411-93F7-000D3A800169′)
–First Parameter is SYSTEMUSERID. Refer Step 2
–Second parameter is ROLEID. Refer Step 4

Step 6: Reset IIS. Logged in CRM with System Admin User and it worked.

Hope this article helped you and share your valuable feedback on this article.

Advertisements

2 thoughts on “How to assign System Administrator role for a user in CRM using SQL?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.