The CRM Grid – A Microsoft CRM Blog

Greg Owens’ rose-tinted MS-CRM mumblings

Deleting Users in MS CRM 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.

Advertisements

53 Comments»

  kreed wrote @

Wow. This script is pretty dangerous! I hope people read the article before stealing your script.

  Greg Owens wrote @

Absolutely it is – in fact I think I’ll add some more bold warnings to the start.

  Ivan wrote @

Im pretty sure you can also press next and skip the mapping prompt. That is more of a warning than a stop prompt, they should change the icon.

  Donald wrote @

Can you mail me this apparently very dangerous script please

  Steveo wrote @

Hello can I also have a copy of the script, I am importing a large existing databases into my VM and after applying all the hotfixes etc and three days later I am still looking at the ‘Update Users’ message on the import organization wizard. I don’t need any of the users for what I want to do. Thanks (hopefully in advance)

  -kNuCkleS- wrote @

Hello, thank you for the post.
I’m interested in the script too, can you send me the query please?

  Deniz wrote @

Hi,

Can I have also this script please ?, it can be dangerous but thats the way of life, if used carefully, why not using it..thanks in advance

  Prakash wrote @

HI,

Could you please give me your scripts for deleting users as it is highly mandatory to me in the current situation.

Regards,
Prakash

  Pradeep Ranganath wrote @

Can you please send me the scripts. I am very much in need of this.

  sudheer wrote @

can u please send me the script

  Paul Greg wrote @

Could you please send me the script. I need to delete a user.

  Mario Butler wrote @

Could I get the script please for deleting CRM users.

I’m having an issue with importing an organization into a development environment from production where 4 users were deactivated and removed from active directory and I can’t get past the user mapping screen.

  Greg Owens wrote @

Mario are you sure you need this? As Ivan points out (comment #3), despite CRM showing an error message during import, it is possible to continue even though your 4 users no longer exist.

Please confirm if you still have a problem 🙂

  Mahbubur Bhuiyan wrote @

Would you please send me the script to delete CRM users. Thanks

  Abhishek Ranjan wrote @

Hi,
Can you please send me the script for deleting the users?

Thanks

  Kathy Jantz wrote @

Could I get a copy of the script to delete CRM users? Thanks.

  Greg Owens wrote @

Many thanks to Kathy – unbelievably, the first person out of the many I have sent this script to, who actually had the courtesy to acknowledge receipt and thank me.

  Jason wrote @

Could i have a copy of the script to delete CRM users?

I’m working on an unsupported method of inserting users who no longer have AD accounts but need to be migrated from a legacy system, and being able to delete a user is clutch for my testing purposes!!

Thanks!

  Carlos wrote @

Please send me the script. Thanks.

  jabacrm wrote @

Thanks for your work. Please send me the script as I have an unusual issue – two users were set up in AD with the same email address, and CRM now has dupe user email addresses. This is causing issues with validation for Outlook Appointments.

Thanks.

  Greg Owens wrote @

Happy to send you the script but are you sure you need it? You could edit the user record in CRM (Settings > Administration > Users) and change the email address on one of the records. Alternatively, just deactivating one of the user records in CRM (if it is not needed) would suffice.

Let me know if the script is still needed 🙂

  Tin wrote @

Hi Can I also have the script? I have issue with the user account.

Thanks 🙂

  Karthik wrote @

Hi there,

Can you please send me this scrip as soon as possible. I have a user account issue on my current CRM 4 system regarding enabling/disabling and want to try this out.

Cheers 🙂

  Jonas wrote @

Can I get your script please.thanks.

  Amit wrote @

Hi,
If I choose to perform the import organization procedure, (and ignore the warning regarding users that are not mapped), what will happen to these users records, and their related (owned) records in the upgraded CRM?
Are these records deleted during the import organisation procedure? and if not, will these records still be accessible in the CRM?

Thanks,
Amit.

  Mark wrote @

Hello there, great article. Could i get a copy of the script please? Many thanks in advance.

  Parag Bhambure wrote @

Could I get a copy of the script to delete CRM users? Thanks

  Chris wrote @

Could I please get a copy of the script? Many thanks!

  Nabile wrote @

Please, could I have your SQL script to delete the user too?

  sebastian.mayer.67 wrote @

Hi, I would like to get the script.

  Sumit wrote @

Could I get the Copy of the scripts please, Thanks in Advance

  jm wrote @

can you send to me please? This is crazy that they won’t build a delete function that auto-reassigneds, cleans up, and lets us maintain our sanity !!! I am not looking forward to doing an actual upgrade to 2011..

  Alfons wrote @

Could I please have a copy of that script.

  Vikas wrote @

Could I please have a copy of that script.

  Bill wrote @

I would be very grateful if you can send us a copy of the script please. Thanks.

  Yuki wrote @

Please send me a copy of the script.
[Scribe seems to have issues based on CRM user count [disabled and enabled] being higher than the license even though the Active count of users is within our license.]

  Srini R wrote @

Please send the script to me too

  Charles wrote @

Could you please send me the script. Thank you!

  Jake wrote @

Hello, may i have the script as well please

  Thomas Dassel wrote @

Hello,

can you please send me the script also ? I urgently need to delete a double entry.

  Janneth Luna wrote @

Hi,
Please can you send me the script. Thanks.

  Shakthi wrote @

Can you please send me the Script. Thanks

  Retrograde wrote @

Greg, Please can you email me a copy of this script as we have had an incident with Active Directory, which I have managed to fix, however we lost about 30 user accounts in the process and although I have re-created them in AD … CRM keeps telling me that they exist although it won’t let me disable the account, i’m hoping that deleting them will allow me to re-create them…

  Guilherme wrote @

Hi,
I would make good use of this script.
Can you share it with me?
Thanks.

  Keith Walkley wrote @

Greg, sorry to be a pain but please, please, pretty please can you email me a copy of this script as we have had an incident with Active Directory, which I have managed to fix, however we lost about 30 user accounts in the process and although I have re-created them in AD … CRM keeps telling me that they exist although it won’t let me disable the account, i’m hoping that deleting them will allow me to re-create them…

  Greg Owens wrote @

Hi Keith. No pain – script duly sent, though I fear in your case it may not help since the script reassigns records to users (amongst other things).

Suggest you take a look in the database at the dbo.systemuserbase table – as I understand it, the check for existing users simply looks at DomainName and (possibly) ActiveDirectoryGuid. You may be able to repair something from there rather than using this agressive script. I don’t remember now where the ActiveDirectoryGuid value comes from though. If you’re stiuck, enable tracing on the server at the point you try to re-create your user and you should be able to see what queries the platform is running.

  Priscilla wrote @

I need to get a copy of the script to delete users. I have a new install of CRM 2011 and I copied users from my CRM 4 deployment incorrect without using the migration or scribe software. So the user records are not editable because they do not exist. I just need to clear my SystemUserBase table.

  Raj wrote @

I need to delete a CRM User which was created for test purpose and is not associated with a domain account. Could you please email me the SQL Script to delete the user?

  Jennifer wrote @

Can you please send me your script. I set up a development organization by importing from production. I didn’t map all the users during the import, now the un-mapped users show up in CRM, but they can’t access the organization. I think I need to delete them and re-add them in CRM. If you know there’s a way to avoid running the script, please let me know. Thanks very much.

  Jennifer wrote @

I re-did the import and manually mapped all users except one that kept erroring out on. I am not getting any help from the network people to check on this user’s AD permission. I think I need to delete him, and add him again.

Please send me the delete script, thank you in advance.

  dominique wrote @

Hello Greg
Thanks for your interesting script and maybe you can help me :

my customer tried to destroy 2 users in crm, of course in production.
of course, he did not remove every thing and of course some errors occurs (today it has beeen identified for appointement creation).

of course, it is bad and he might not do it but .. too late.
Now I would help him to get a clean crm with data integrety , as such as possible
I would like your feeling :
– could it be a solution to use your script to remove completly this 2 users ?
– do you think that it need some changing in your script to perform this action or could it be used unchanged?
– could you identify exactly the risk to use it in production (or to say it with other word, did you experience critical behaviour after having used this script?

Thanks for your feed-back

Dominique

  dominique wrote @

me again sorry,
about “do you think that it need some changing in your script to perform this action or could it be used unchanged?” of course I have identified that your script loop over several user and need to be changed for a single user removing. my question was about each individual user removing.

thanks in advance for your feedback

Dominique

  Sam wrote @

HI Greg,

Could you please email me the SQL Script to delete the user? , thank you very much.

cheers


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: