Question:

The DA administrator that installed and setup DA is no longer with the company. How do we replace that account with a valid one so that administration can resume?

Answer:

Here are all the steps needed, 
 
Important 
1. Following steps will completely replace the original DA administrator with a new one. All permissions of the original DA administrator including permissions over the DA installation, Audit Store(s), Queries etc. will get revoked at the end of this exercise. 
 
Pre-requisites
1. Using SQL management studio, connect to the database server that's hosting DA Management Database and create a SQL login for the new intended DA administrator. This new login does not need to have any administrative privileges on the database server. 
 
Steps (All done using SQL management studio by a user with sysadmin rights on the database server), 
 
1. Note down SID of the old DA administrator

Run following SQL query against the DA Management Database:

SELECT TrusteeSid, SUSER_SNAME(TrusteeSid) AS Name FROM dbo.InstallationPermission 

Above command will return the current set of users that have any kind of permission on the DA installation. Note down the value of TrusteeSid column from the query result that corresponds to the old DA administrator. This value will be referred to as <OLD_SID> for the rest of this KB. Sample output of this SQL query is given below, 

TrusteeSid Name   0x0105000000000005150000004D492851C328E2E82311D4384A080000 ACME\user1 

 
2. Add the new DA administrator (referred to as <NEW_DA_ADMIN> for the rest of the KB) to the "user" database role of the DA Management Database by running following SQL command against the DA Management Database (this is the same user whose SQL login was mentioned in pre-requisites section). Please make sure the user name is in domain\username format. 

EXEC sp_addrolemember 'user', '<NEW_DA_ADMIN>' 
e.g. EXEC sp_addrolemember 'user', 'CORP\NewDAAdmin' 
 

3. Now run following SQL commands one after another by replacing <OLD_SID> with SID of the original DA administrator (noted down in step#1) and replacing <NEW_DA_ADMIN> with username of the new DA administrator, 

    UPDATE AuditRoleAssignment SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE AuditRolePermission SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE AuditStoreAdministrator SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE AuditStorePermission SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE InstallationPermission SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE ManagementDatabasePermission SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE Query SET OwnerSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE OwnerSid = <OLD_SID> 
    UPDATE QueryPermission SET TrusteeSid = SUSER_SID('<NEW_DA_ADMIN>') WHERE TrusteeSid = <OLD_SID> 
    UPDATE SystemProperty SET Value = CONVERT(sql_variant, SUSER_SID('<NEW_DA_ADMIN>')) WHERE Name = 'Administrator' 
 
    e.g. 
    UPDATE AuditRoleAssignment SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE AuditRolePermission SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE AuditStoreAdministrator SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE AuditStorePermission SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE InstallationPermission SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE ManagementDatabasePermission SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE Query SET OwnerSid = SUSER_SID('CORP\NewDAAdmin') WHERE OwnerSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE QueryPermission SET TrusteeSid = SUSER_SID('CORP\NewDAAdmin') WHERE TrusteeSid = 0x0105000000000005150000004D492851C328E2E82311D438F4010000 
    UPDATE SystemProperty SET Value = CONVERT(sql_variant, SUSER_SID('CORP\NewDAAdmin')) WHERE Name = 'Administrator'