Purge no longer running after DCS upgrade, SQL environment change, or permission changes to SQL users

Products

• Data Center Security Server Advanced

Issue/Introduction

You have made permissions changes or migrated your DCS manager from one SQL environment to another.

xxxxxxx 2017-XX-XX 05:00:11.111 [TRACE] [Timer-3:72] [SQL:229] [SPID:52] [TID:441] Unable to get a module lock for 'SisDbCleanup_Lock' java.sql.SQLException: The EXECUTE permission was denied on the object 'sp_getapplock', database 'mssqlsystemresource', schema 'sys'.

Cause

You have changed your permissions from their default value, or when migrating your permissions there was an error.

Resolution

The following SQL query will list out your data base permissions:

 

USE master;

CREATE TABLE #Temp_Sphelprotect
(
   Owner nvarchar(200),
   Object nvarchar(200),
   Grantee nvarchar(200),
   Grantor nvarchar(200),
   ProtectType nvarchar(200),
   Action nvarchar(200),
   Columns nvarchar(200)
);

INSERT INTO #Temp_Sphelprotect
EXEC sp_helprotect;

SELECT * FROM #Temp_Sphelprotect

SELECT * FROM #Temp_Sphelprotect
WHERE Object = 'sp_getapplock';

DROP TABLE #Temp_Sphelprotect;

This issue is present if your Existing permission is not like as seen below:

sys sp_getapplock public dbo Grant     Execute .

To change this value run the following only after BACKING UP YOUR DATABASE FIRST.

USE master;
GRANT EXECUTE on sp_getapplock to public;

After making this change rerun "get_master_permission.sql" shared in previous
comment. Verify the permissions of "sp_getapplock".

A restart of the manager service is required for this change to take affect.