My apologies, I missed a section of the first part of the SQL statement. Please be aware that any updates you are making are at your own risk and I take no liability for anything that may occur to your system. If you are able to run the first section of code without errors, then continuing to the transactional statement is up to you. The first portion is a query only so no changes are made. Please review the following updated statements:
DECLARE @loginname varchar(50) = 'domain\user'
DECLARE @sid varchar(50) = 'insert SID retrieved in PowerShell'
DECLARE @UserId uniqueidentifier;
SELECT @UserId = Id
FROM MSCRM_CONFIG.dbo.SystemUser
WHERE Name = @loginname
DECLARE @suaID uniqueidentifier;
SELECT @suaID = Id
FROM MSCRM_CONFIG.dbo.SystemUserAuthentication
Where UserId = @UserId and AuthInfo like 'W:%'
SELECT su.Id, su.IsDisabled, su.Name, su.IsDeleted, sua.AuthInfo, sua.UserId, sua.IsDeleted
FROM MSCRM_CONFIG.dbo.SystemUser su LEFT OUTER JOIN
MSCRM_CONFIG.dbo.SystemUserAuthentication sua ON su.Id = sua.UserId
WHERE su.Name = @loginname
SELECT @loginname [@loginname], @sid [@sid], @UserId [@UserId], @suaID [@suaId]
This query should give you two separate results, the first is the joining of the MSCRM_Config SystemUser & SystemUserAuthentication tables. The second is to confirm that the declared variables are the ones needed - the correct domain\loginname, correct SID, the UserId (same between both tables), and the unique ID for the SystemUserAuthentication table for the record that contains the SID. You should compare the sua.AuthInfo value against the @sid value. Ignoring the W:, if the values do not match, this is why you cannot log into the Deployment Manager with the new account. The next steps are completely at your own risk, no liability or expectations on my end.
BEGIN TRAN
UPDATE MSCRM_CONFIG.dbo.SystemUser
SET IsDisabled = NULL, IsDeleted = 0
WHERE Name = @loginname
GO
UPDATE MSCRM_CONFIG.dbo.SystemUserAuthentication
SET AuthInfo = 'W:' @sid, IsDeleted = 0
WHERE ID = @suaId
ROLLBACK
As long as all variables are added correctly, the transactional statement is committed, and you were able to confirm everything from the first statement, this should allow you to access the deployment manager as the user and add in a second deployment administrator. I cannot advise if you should or should not do these steps - if you are concerned, please engage a DBA through your organization to confirm the information prior to executing. As always, it is HIGHLY ENCOURAGED that you back up the organizational CRM database (ORGNAME_MSCRM) as well as the MSCRM_CONFIG database PRIOR to making any changes/updates.
If the above resolves your issue, please do mark this as the verified answer
