Deleting Users in MS CRM 4.0

April 4, 2011 at 11:23 UTC · Filed under MSCRM 4.0

I have a SQL script (see below) which will allow you to delete a user fully and completely from Microsoft Dynamics CRM 4.0. It is, of course, utterly wrong and unsupported to do this, but still – the script exists and seems to work just fine.

I originally wrote it to carry out some particularly unsupported work with a previous client. Later on, I found use for it on more than one occasion during my own development (specifically when importing clients’ existing databases into my own Virtual Machines).

Some time ago, I pulled the script from here but I can no longer keep up with requests send it on. Consequently you can now find it at the end of this post.

I chose to remove it since even my own tenuous example of how it might be useful turned out to be overengineered. I had suggested that deleting users might be an easy option when importing databases full of users into a development environment, but where you the developer don’t want the overhead of creating AD accounts and then mapping those users. Ivan (comments, below) has correctly pointed out behaviour which I was previously aware of but had slipped my memory: mapping users is not mandatory during the “Import Organisation” process (well, you need to map at least one user, but otherwise…). Despite showing an “Error” during the mapping process, so long as at least one user has been mapped, one can opt to ignore the issue and proceed with the import. Thank  you Ivan.

In my opinion, there are very few (if any?) viable reasons to use this script, so I recommend that you exhaust all other options before considering using this script. Be aware that the reason you can’t delete a user in CRM is that there are usually different ways to approach the problem.

Do not use this (or anything like it) for anything other than a one-off or temporary/test/dev situation however…:

The script below requires that you nominate a single user that you will retain from the “old” system. All records will effectively be allocated to this user and when you import the data to your DEV system, you only need to map one user. I only ever use the Deployment Administrator (so be warned, I’ve not tested this script in other scenarios). It will change ownership on anything connected to the users you are deleting. It goes without saying that this is highly, massively and utterly unsupported!

The script should be run from your Organisation_MSCRM database, in SQL Management Studio.

--********************************************************
--**** DANGEROUS SCRIPT - TAKE A BACKUP OF YOUR DB ****
--**** AND DON'T EVER RUN THIS IN PRODUCTION! ****
--********************************************************

DECLARE @UserToRetain_DomainName varchar(128), @UPDATESTATEMENT VARCHAR(MAX), @GUID uniqueidentifier, @INHERITEDGUID uniqueidentifier, @USERNAME VARCHAR(128)

--**** SET VARIABLES HERE **********************************
SET @UserToRetain_DomainName = 'domain\CRM_Administrator'
--**********************************************************

SELECT @INHERITEDGUID = systemuserid FROM dbo.systemuserbase WHERE
domainname = @UserToRetain_DomainName

IF @INHERITEDGUID IS NOT NULL
BEGIN
SET NOCOUNT ON
SET @username = ''
WHILE @USERNAME IS NOT NULL
BEGIN
SET @GUID = (
SELECT MIN(cast(systemuserid as varchar(40)))
FROM dbo.systemuserbase
WHERE domainname <> @UserToRetain_DomainName
AND domainname IS NOT NULL
AND FullName NOT IN ('SYSTEM','INTEGRATION')
)
SET @USERNAME = (SELECT domainname FROM dbo.systemuserbase WHERE systemuserid = @GUID)
SELECT 'Deleting user: ' + @username

/* Delete basic records */
DELETE FROM TeamMembership WHERE SystemUserId =@GUID
DELETE FROM InternalAddressBase WHERE ParentId =@GUID
DELETE FROM PrincipalObjectAccess WHERE ObjectId =@GUID
DELETE FROM PrincipalObjectAccess WHERE PrincipalId =@GUID
DELETE FROM QueueItemBase WHERE QueueId IN (SELECT QueueId FROM QueueBase WHERE PrimaryUserId =@GUID )
DELETE FROM QueueBase WHERE PrimaryUserId =@GUID
DELETE FROM ResourceBase WHERE ResourceId =@GUID
DELETE FROM SystemUserExtensionBase WHERE SystemUserId =@GUID
DELETE FROM SystemUserPrincipals WHERE PrincipalId =@GUID
DELETE FROM SystemUserPrincipals WHERE SystemUserId =@GUID
DELETE FROM UserQueryBase WHERE ModifiedBy =@GUID
DELETE FROM UserQueryBase WHERE OwningUser =@GUID
DELETE FROM UserSettingsBase WHERE SystemUserId =@GUID

/* Change createdby/modifiedby/owninguser to another user */
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName,
2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('uniqueidentifier', 'char',
'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME)> @ColumnName
AND QUOTENAME(COLUMN_NAME) IN
('[createdby]','[modifiedby]','[owninguser]','[recordsownerid]')
)

IF @ColumnName IS NOT NULL
BEGIN
SET @UPDATESTATEMENT = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = ''' + CAST(@INHERITEDGUID AS
NVARCHAR(40)) +
''' WHERE ' + @ColumnName + ' = ''' + CAST(@GUID AS NVARCHAR(40)) + ''''
EXEC(@UPDATESTATEMENT)
END
END
END

/* Delete user roles prior to deleting user */
DELETE FROM SystemUserRoles WHERE SystemUserId = @GUID
/* Ensure user to be deleted is not a manager for any other user */
UPDATE SystemUserBase SET ParentSystemUserId = NULL WHERE ParentSystemUserId =@GUID
/* Ensure user to be deleted is not the Impersonating User for any plugins */
UPDATE SdkMessageProcessingStepBase SET ImpersonatingUserId = @INHERITEDGUID WHERE ImpersonatingUserId =@GUID
/* Delete the system user record from Org_MSCRM database */
DELETE FROM SystemUserBase WHERE SystemUserId =@GUID
/* Delete calednar rules prior to deleting calendar */
DELETE FROM CalendarRuleBase WHERE CalendarId IN (SELECT CalendarId FROM CalendarBase WHERE PrimaryUserId =@GUID)
/* Foreign Key contraints force calendar to be deleted after user */
DELETE FROM CalendarBase WHERE PrimaryUserId =@GUID
END
END

Points of note

•             This script deletes all (except one) users. You should be able to tweak

this very easily to just delete a single, specified user.

•             If you’ve made any custom relationships between any of the tables

explicitly listed in the script then you may need to add extra clauses to avoid FOREIGN KEY CONTRAINT errors (e.g. relationship between a custom entity and the Queue entity) . Usually this will simply throw an error and require you to resolve the issue before proceeding.

About these ads

Permalink