Purge is not working any more on our DCS manager.


Article ID: 169939


Updated On:


Data Center Security Server Advanced


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'.


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


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.