删除CRM Dynamics 2011中的用户

本文关键字：用户 2011 CRM Dynamics 删除 | 更新日期: 2023-09-27 18:18:53

我很清楚Microsoft不支持在CRM Dynamics 2011中删除用户(SystemUser Entity)。

然而，我们目前正在开发一个工具来支持我们的用户配置需求。为了能够为这个工具编写集成测试，似乎有必要能够在之后删除用户，以便我们可以将测试环境回滚到原始状态。目前，我们通过从SQL备份中恢复组织来实现这一点，但是对于每个测试运行来说，这太耗时了。

更新到目前为止，我们拥有的最佳解决方案是在集成测试中创建一个用户，断言我们需要断言的一切，然后通过禁用该用户并删除其AD凭据来"清理它"，以便我们可以在下一次运行测试时重用这些凭据。

然而，因为我们只是在寻找一个测试环境的解决方案，我真的很想有一个解决方案，清理一切正确:删除SQL中的记录似乎是要走的路。由于复杂的数据库结构，然而，我希望有人可以提供这个脚本。

更新2

我们已经创建了从SQL中手动删除用户的脚本(参见已接受的答案)。是不支持的，所以只有在测试环境中使用它，如果你知道你在做什么。

删除CRM Dynamics 2011中的用户

以下脚本不被Microsoft支持。使用它可能会伤害、破坏、炸毁或骚扰您的CRM组织、部署、服务器和职业。千万不要用这个。
。

话虽这么说，我们使用了这个，它很好地满足了我们的目的:在运行AddSystemUser测试后清理我们的测试环境。

其他需要注意的事项:

  • 我们使用CRM Dynamics 2011 UR10 On-Premises
  • 我们的测试用户没有任何相关记录，它只是一个孤立的用户
  • 我们正在使用AD认证
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

也许你可以尝试一下这个工作。与其禁用它们，不如将活动目录名称更改为其他名称，然后禁用这些记录。

例如，您的脚本可以如下所示(假设AD身份验证):

创建AD用户msmith、bmiller和jdoe。

执行测试和验证。

更新 msmith , testuser1
更新 bmiller 到 testuser2
更新 jdoe , testuser3

停用 testuser1 , testuser2 , testuser3

下一个测试将需要使用testuser4、testuser5、testuser6，这意味着您将需要创建相当多的虚拟帐户，但这可能比打乱CRM SQL数据库更容易。

对于我的单元测试，我需要一个用户，我实际上模拟了IOrganizationService调用只是SystemUser请求，并返回一个模拟的SystemUser实体，没有它实际上击中CRM。我也会建议这样做，但是听起来您正在尝试实际测试System User创建，所以在这种情况下可能不是一个选项。

以下是我在CRM 2015中基于Joris代码所做的工作

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

一旦您运行了查询并确保它正在删除正确的数据，您就可以取消COMMIT语句的注释并注释Rollback语句

希望能有所帮助。

只是对Dynamics CRM 2016 (On Premise)进行了一些轻微的修改，以允许我遇到的一些常见的约束错误。同样，这是完全不支持的，您需要自行承担风险。(当我发现其他约束错误时，我会更新)

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

您可以使用SQL完全不支持的方式删除它们。
只要您在集成测试环境中这样做，我认为危害就相对较低。

要查找在添加用户时对数据库所做的更改，可以执行

  • 复制当前数据库
  • 添加用户
  • 比较新的数据库状态与前一个(例如:redgate的SQL比较)

每次rollup都可能发生变化，因此请确保不要依赖于任何关键代码或生产代码。

这是不支持的，但是如何直接从SQL中删除记录?

我自己从来没有尝试过，我也不想在生产环境中这样做，但如果只是为了测试/开发，最坏的情况是你破坏了一个开发环境。

相关文章：