Skip to main content
Planview Customer Success Center

How to grant Tempdb access to multiple SQL accounts (V2012)

Symptoms
When installing Test and Training databases on the same server as per the instructions at page 185 of the Installation guide, having each environment using a separate SQL account, it is not possible to retain Tempdb access for both SQL accounts.

By running EnableSQLDependency for two different SQL accounts, Tempdb access is granted to the last SQL account used in EnableSQLDependency.

How can we maintain Tempdb access for two different SQL accounts, such as CPSQLUser_Test and CPSQLUser_Train?
 


Resolution
In v2012 the tempdb access commands were moved into EnableSQLDependency script, therefore the access is granted to the last SQL account used in EnableSQLDependency.

Before running EnableSQLDependency edit the respective section at the end which creates GrantTempdbAccess, by adding "_CPSQLUser" to the end of the procedure name:

Change from:

IF EXISTS(SELECT 1 FROM sys.objects where Name = 'GrantTempdbAccess' AND type = 'P')
DROP PROC GrantTempdbAccess
{...}

CREATE PROC GrantTempdbAccess
AS
{...}

EXEC GrantTempdbAccess
{...}

EXEC SP_PROCOPTION 'GrantTempdbAccess', 'startup', 'on'
{...}

To:

IF EXISTS(SELECT 1 FROM sys.objects where Name = 'GrantTempdbAccess_CPSQLUser' AND type = 'P')
DROP PROC GrantTempdbAccess_CPSQLUser
{...}

CREATE PROC GrantTempdbAccess_CPSQLUser
AS
{...}

EXEC GrantTempdbAccess_CPSQLUser
{...}

EXEC SP_PROCOPTION 'GrantTempdbAccess_CPSQLUser', 'startup', 'on'
{...}

After this change, when “CPSQLUser” is replaced by different SQL accounts as suggested in the documentation, there will be unique startup stored procedures for each SQL account.