Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >

How to recover administrator access to an existing DirectAudit Installation

11 April,19 at 11:50 AM

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. for DirectAudit 2.0

USE [%DatabaseName%]

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

-- Create login for Windows account
IF NOT EXISTS(SELECT from sys.server_principals WHERE = @username)

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

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

-- 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
   @count OUTPUT

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