Removing Users in CRM Dynamics 2011

Question

I fully understand that removing users (SystemUser Entity) in CRM Dynamics 2011 is not supported by Microsoft.

However, we are currently developing a tool to support our user needs. In order to be able to record integration tests for this tool, it seems necessary to be able to subsequently remove users so that we can roll back our test environment to its original state.
We are currently doing this by restoring organizations from SQL backups, but it is too long to complete each test run.

Update

So far, the best solution has been to create a user in the integration test, approve everything that we would need to approve, and then “clear” by disconnecting the user and deleting his AD credentials so that we can reuse them - use these credentials for the next test run .

However, since we are only looking for a solution for a test environment, I would really like for you to have a solution that clears everything correctly: deleting records in SQL seems to be the way to go. However, due to the complex structure of the database, I was hoping that someone could provide scripts for this.

Update2

We created a script to manually remove the user from SQL (see accepted answer). This is not supported , so use it only in test environments if you know what you are doing.

Answer 1

The following script is not supported by Microsoft. Using this can harm, brick, explode, or bring your CRM organization, deployment, server, and career.
Never use this. Ever.

We used this, and it worked great for our purpose: cleaning up the test environment after running AddSystemUser tests.

Some other things to keep in mind:

• We use CRM Dynamics 2011 UR10 On-Premises
• Our test user has no related records, he is just an isolated user
• We use AD authentication

 USE OrganizationName_MSCRM BEGIN TRANSACTION DECLARE @username AS VARCHAR(50) -- CHANGE THIS -- SET @username = 'domain\username' -- DONT CHANGE ANYTHING AFTER THIS -- DECLARE @userId AS UNIQUEIDENTIFIER SET @userId = (SELECT SystemUserId FROM dbo.SystemUserBase WHERE DomainName = @username) DECLARE @orgid AS UNIQUEIDENTIFIER SET @orgid = (SELECT OrganizationId FROM dbo.SystemUserBase WHERE systemuserid = @userid) DECLARE @userEmail AS VARCHAR(MAX) SET @useremail = (SELECT InternalEMailAddress FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @userfullname AS VARCHAR(max) SET @userfullname = (SELECT fullname FROM dbo.systemuserbase WHERE systemuserid = @userid) DECLARE @queueid AS UNIQUEIDENTIFIER SET @queueid = (SELECT queueid FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @ownerid AS UNIQUEIDENTIFIER SET @ownerid = (SELECT ownerid FROM dbo.OwnerBase WHERE name = @userfullname) DELETE FROM dbo.SystemUserExtensionBase WHERE SystemUserId = @userId DELETE FROM dbo.UserSettingsBase WHERE SystemUserId = @userId DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserPrincipals WHERE systemuserid = @userId DELETE FROM dbo.SystemUserRoles WHERE systemuserid = @userId DELETE FROM dbo.SystemUserBusinessUnitEntityMap WHERE systemuserid = @userid DELETE FROM dbo.UserQueryBase WHERE OwnerId = @userid DELETE FROM dbo.SystemUserProfiles WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserBase WHERE SystemUserId = @userid DELETE FROM dbo.QueueBase WHERE QueueId = @queueid DELETE FROM dbo.PrincipalEntityMap WHERE PrincipalId = @ownerid DELETE FROM dbo.PrincipalObjectAccess WHERE principalid = @ownerid DELETE FROM dbo.OwnerBase WHERE ownerid = @ownerid DELETE FROM dbo.EmailSearchBase WHERE EmailAddress = @userEmail DELETE FROM dbo.ResourceBase WHERE name = @userfullname DELETE FROM dbo.CalendarRuleBase WHERE CalendarId IN (SELECT CalendarId FROM dbo.CalendarBase WHERE PrimaryUserId = @userid) DELETE FROM dbo.CalendarBase WHERE primaryuserid = @userId DELETE FROM dbo.InternalAddressBase WHERE parentid = @userId DELETE FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid COMMIT 

Answer 2

Perhaps you can try this work. Instead of just turning them off, change the name of the active directory to another, and then turn off the entries.

For example, your script might look like this (assuming AD authentication):

Create AD users msmith , bmiller, and jdoe .

Perform tests and validation.

Update msmith to testuser1
Update bmiller testuser2
Update jdoe to testuser3

Deactivate testuser1 , testuser2 , testuser3

The next test will have to use testuser4 , testuser5 , testuser6 , which means that you need to create a lot of dummy accounts, but this might be easier than confusing the CRM SQL database.

For my unit tests, in which I need a User, I actually scoff at calling IOrganizationService only for SystemUser requests and return the mocked SystemUser system without actually getting into CRM. I would suggest this too, but it looks like you are trying to actually test the creation of System User, so this is probably not an option in this case.

Answer 3

Here's what I did in CRM 2015 based on Joris code

 USED ORGANIZATIONNAME_MSCRM BEGIN TRANSACTION DECLARE @username AS VARCHAR(50) -- CHANGE THIS -- SET @username = 'AD\USERNAME' -- DONT CHANGE ANYTHING AFTER THIS -- DECLARE @userId AS UNIQUEIDENTIFIER SET @userId = (SELECT SystemUserId FROM dbo.SystemUserBase WHERE DomainName = @username) DECLARE @orgid AS UNIQUEIDENTIFIER SET @orgid = (SELECT OrganizationId FROM dbo.SystemUserBase WHERE systemuserid = @userid) DECLARE @userEmail AS VARCHAR(MAX) SET @useremail = (SELECT InternalEMailAddress FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @userfullname AS VARCHAR(max) SET @userfullname = (SELECT fullname FROM dbo.systemuserbase WHERE systemuserid = @userid) DECLARE @queueid AS UNIQUEIDENTIFIER SET @queueid = (SELECT queueid FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @ownerid AS UNIQUEIDENTIFIER SET @ownerid = (SELECT ownerid FROM dbo.OwnerBase WHERE name = @userfullname) DECLARE @MSCRMUserID as UNIQUEIDENTIFIER SET @MSCRMUserID = (SELECT Userid FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid) DECLARE @authinfo as NVARCHAR(255) SET @authinfo = (SELECT Authinfo FROM mscrm_config..SystemUserAuthentication WHERE Userid = @MSCRMUserID) DECLARE @SUid as UNIQUEIDENTIFIER SET @Suid = (SELECT id FROM mscrm_config..SystemUserAuthentication WHERE Userid = @MSCRMUserID) DELETE FROM dbo.UserSettingsBase WHERE SystemUserId = @userId DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserPrincipals WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserRoles WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserBusinessUnitEntityMap WHERE systemuserid = @userid DELETE FROM dbo.UserQueryBase WHERE OwnerId = @userid DELETE FROM dbo.SystemUserProfiles WHERE SystemUserId = @userId DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId DELETE FROM dbo.QueueMembership WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserBase WHERE SystemUserId = @userid DELETE FROM dbo.QueueBase WHERE QueueId = @queueid DELETE FROM dbo.PrincipalEntityMap WHERE PrincipalId = @ownerid DELETE FROM dbo.PrincipalObjectAccess WHERE PrincipalId = @ownerid DELETE FROM dbo.MailboxBase WHERE OwnerId = @ownerid DELETE FROM dbo.OwnerBase WHERE OwnerId = @ownerid DELETE FROM dbo.EmailSearchBase WHERE EmailAddress = @userEmail DELETE FROM dbo.ResourceBase WHERE name = @userfullname DELETE FROM dbo.CalendarRuleBase WHERE CalendarId IN (SELECT CalendarId FROM dbo.CalendarBase WHERE PrimaryUserId = @userid) DELETE FROM dbo.InternalAddressBase WHERE parentid = @userId DELETE FROM dbo.CalendarBase WHERE primaryuserid = @userId DELETE FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid DELETE FROM mscrm_config..SystemUserAuthentication WHERE authinfo = @authinfo DELETE FROM mscrm_config..SystemUser WHERE id = @MSCRMUserID rollback --COMMIT 

Once you run the request and make sure that it deletes the necessary data, you can cancel the comment of the COMMIT instruction and comment on the rollback operator

Hope this helps.

Answer 4

Just some small changes for Dynamics CRM 2016 (On Premise) to resolve some common constraint errors that I encountered. Again, this is not fully supported and is at your own risk. (I will update when I discover other restriction errors.)

 BEGIN TRANSACTION DECLARE @username AS VARCHAR(50) /* CHANGE THIS LINE ONLY */ SET @username = 'DOMAIN\USERNAME' /* END CHANGES */ DECLARE @userId AS UNIQUEIDENTIFIER SET @userId = (SELECT SystemUserId FROM dbo.SystemUserBase WHERE DomainName = @username) DECLARE @orgid AS UNIQUEIDENTIFIER SET @orgid = (SELECT OrganizationId FROM dbo.SystemUserBase WHERE systemuserid = @userid) DECLARE @userEmail AS VARCHAR(MAX) SET @useremail = (SELECT InternalEMailAddress FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @userfullname AS VARCHAR(max) SET @userfullname = (SELECT fullname FROM dbo.systemuserbase WHERE systemuserid = @userid) DECLARE @queueid AS UNIQUEIDENTIFIER SET @queueid = (SELECT queueid FROM dbo.SystemUserBase WHERE SystemUserId = @userid) DECLARE @ownerid AS UNIQUEIDENTIFIER SET @ownerid = (SELECT ownerid FROM dbo.OwnerBase WHERE name = @userfullname) DECLARE @MSCRMUserID as UNIQUEIDENTIFIER SET @MSCRMUserID = (SELECT Userid FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid) DECLARE @authinfo as NVARCHAR(255) SET @authinfo = (SELECT Authinfo FROM mscrm_config..SystemUserAuthentication WHERE Userid = @MSCRMUserID) DECLARE @SUid as UNIQUEIDENTIFIER SET @Suid = (SELECT id FROM mscrm_config..SystemUserAuthentication WHERE Userid = @MSCRMUserID) DELETE FROM dbo.UserSettingsBase WHERE SystemUserId = @userId DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserPrincipals WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserRoles WHERE SystemUserId = @userId DELETE FROM dbo.SystemUserBusinessUnitEntityMap WHERE systemuserid = @userid DELETE FROM dbo.UserQueryBase WHERE OwnerId = @userid DELETE FROM dbo.SystemUserProfiles WHERE SystemUserId = @userId DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId DELETE FROM dbo.QueueMembership WHERE SystemUserId = @userId update dbo.SdkMessageFilterBase set ModifiedOnBehalfBy = NULL where ModifiedOnBehalfBy = @userid update dbo.SdkMessageFilterBase set CreatedOnBehalfBy = NULL where CreatedOnBehalfBy = @userid DELETE FROM dbo.SystemUserBase WHERE SystemUserId = @userid DELETE FROM dbo.CalendarRuleBase WHERE CalendarId IN (SELECT CalendarId FROM dbo.CalendarBase WHERE PrimaryUserId = @userid) DELETE FROM dbo.CalendarBase WHERE primaryuserid = @userId DELETE FROM dbo.QueueBase WHERE QueueId = @queueid DELETE FROM dbo.PrincipalEntityMap WHERE PrincipalId = @ownerid DELETE FROM dbo.PrincipalObjectAccess WHERE PrincipalId = @ownerid DELETE FROM dbo.UserEntityUISettingsBase WHERE OwnerID = @userid DELETE FROM dbo.UserApplicationMetadataBase WHERE OwnerID = @userid DELETE FROM dbo.PostFollowBase WHERE OwnerID = @userid DELETE FROM dbo.MailboxBase WHERE OwnerId = @ownerid DELETE FROM dbo.OwnerBase WHERE OwnerId = @ownerid DELETE FROM dbo.EmailSearchBase WHERE EmailAddress = @userEmail DELETE FROM dbo.ResourceBase WHERE name = @userfullname DELETE FROM dbo.InternalAddressBase WHERE parentid = @userId DELETE FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid DELETE FROM mscrm_config..SystemUserAuthentication WHERE authinfo = @authinfo DELETE FROM mscrm_config..SystemUser WHERE id = @MSCRMUserID /* rollback */ COMMIT 

Answer 5

This is not supported, but how to delete records directly from SQL?

I have never tried this myself, and I would not want to do it in a production environment, but if it's just for testing / development, then the worst thing that happens is to cut out the development environment.

Answer 6

You can use a completely unsupported way to remove them using SQL.
While you are doing this in a test integration environment, I think the harm will be relatively low.

To find out what changes were made to the database when adding a user, you could

• copy the current database
• add user
• compare the new state of the database with the previous one (for example: redgate SQL compare)

This can happen with every collapse, so you should not rely on it for any critical or production code.