Find out how to recover administrator access to an existing DirectAudit Installation by granting an AD User administrator privileges at the Database level. 

DirectAudit Installation Administrator

Working to deploy Centrify software since nearly 10 years, I have been generally occupied delivering expertise and recomendations. But sometimes, I have also saved customers from difficult situation (related or not to Centrify software as a funny fact).

One of those embarrassing situation is when you lose administrator access to a DirectAudit Installation.

First, what is the purpose of the Administrator access? And how is this setup in the first place?

The Administrator of a DirectAudit Installation is this account that have SysAdmin role on the SQL Server running the Management and AuditStore databases, but also have Full management privileges on the application level: e.g. creating new AuditStores, managing Audit Roles, delegating privileges on the Installation.

The account that create an Installation is automatically granted Administrator of this Installation. Centrify Professional Services recommend the following practice to avoid losing access to an Installation due to the Administrator account being decommissioned from Active Directory:

• Use a Service Account to create the DirectAudit Installation, this Service Account lifespan should be attached to DirectAudit
• Grant Administrator Privileges through an AD Group using the DirectAudit Manager Console

In these both recommended cases, you would not risk to lose access to your DirectAudit Installation. But well, we all know that not all deployments are following recommendations. And this person that have setup this DirectAudit Installation may have left the company a while ago, and all you are left with is this DBA Team friend that have access to the SQL Server. So, what now?

Recover Administrator access

The following SQL script can be run using SQL Management Studio with an account that have SysAdmin privileges on the SQL Server. Before to run the script, do a search and replace to add the right values for the AD User account to be granted Administrator permissions, the Database name (Management Database) and the Assembly version.

-- Change %DOMAIN\Username% value to the Principal Name to be granted Administrator permissions on the Management Database, e.g. AASGAARD-CLOUD\fabrice
-- Change %DatabaseName% to the name of the Management Database, e.g. AuditServer-AASGAARD
-- Change %AssemblyVersion% to the version of the Centrify.DirectAudit.GatewayDatabase.xx.xx.x.x assembly version, e.g. 19.12.0.0 for DirectAudit 2.0

USE [%DatabaseName%]
GO

-- Grant permission to user '%DOMAIN\Username%'
DECLARE @username nvarchar(max)
set @username = '%DOMAIN\Username%'

-- Create login for Windows account
IF NOT EXISTS(SELECT sys.server_principals.name from sys.server_principals WHERE sys.server_principals.name = @username)
BEGIN
    CREATE LOGIN [%DOMAIN\Username%] FROM WINDOWS
END

-- Create database user
IF NOT EXISTS(SELECT sys.database_principals.name from sys.database_principals WHERE sys.database_principals.name = @username)
AND NOT EXISTS(SELECT sys.databases.name FROM sys.databases WHERE sys.databases.name = N'%DatabaseName%' and sys.databases.owner_sid = SUSER_SID(@username))
BEGIN
    CREATE USER [%DOMAIN\Username%]
END

-- Add member to database role
IF NOT EXISTS(SELECT sys.databases.name FROM sys.databases WHERE sys.databases.name = N'%DatabaseName%' and sys.databases.owner_sid = SUSER_SID(@username))
BEGIN
    EXEC sp_addrolemember 'user', [%DOMAIN\Username%]
END
GO

-- Set user as administrator
DECLARE @clientVersion nvarchar(255)
DECLARE @trusteeSid varbinary(max)
DECLARE @count int

set @clientVersion = '%AssemblyVersion%'
set @trusteeSid = SUSER_SID('%DOMAIN\Username%')

EXECUTE ManagementDatabaseAdministratorSet
   @clientVersion,
   @trusteeSid,
   @count OUTPUT
GO

DECLARE @saUser nvarchar(255)
SELECT @saUser = [name] FROM sys.server_principals WHERE [sid]=0x01
EXECUTE('ALTER AUTHORIZATION ON DATABASE::[%DatabaseName%] TO [' + @saUser +']')
GO