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.
Wow. This script is pretty dangerous! I hope people read the article before stealing your script.