During the installation, a script is executed for a creation of functions and procedures mentioned below by a SQL Server login that has system administration rights (e.g., the 'sa' login account).
There are approximately 127 such functions created during the installation in the SCSPDB and are required execute permission for the public role. You can run below query on the product database to get the complete list of functions.
SELECT * FROM information_schema.routines WHERE routine_type='FUNCTION' ORDER BY SPECIFIC_NAME asc;
Additionally, 2 more stored procedures (dbo.TstObject and dbo.WriteDebug (within the Stored Procedures of the SCSPDB)) that require 'EXECUTE' access by the public role.
Apart from this, the execute permission is required to execute following two stored procedures. These two stored procedures are part of the master database and execute privileges are granted by default to the public role.
1. exec master..xp_msver
--The stored procedure provides information related database environment.
2. exec master..xp_fixeddrives
-- The stored procedure provides disk space availability on database machine.
This role is created during the installation of DCS 6.7 and later assigned to the user ('the operational user') that is connecting to the database (this user is defined in server.xml). The SCSP_OPSROLE requires execute permission on the following stored procedures: SCSP_AddApplication, SCSP_AddVirtualAgent, SCSP_CatalogUploadEvent, SCSP_CustomHook, SCSP_DBINFO, SCSP_DBSTATUS, SCSP_ExecViewStr, SCSP_GenHealthEvents, SCSP_GetHomePageStats, SCSP_GetKnownApplications, SCSP_GetPolicySettings, SCSP_GetPolicySettingsWrapper, SCSP_GetRemediationComments, SCSP_ManageAppInventory, SCSP_ParseDelimitedList, SCSP_ProcessEvent, SCSP_PurgeEventsUsingDate, SCSP_PurgePartitionedEvents, SCSP_SavePolicyRef, SCSP_ServerAction, SCSP_SetToken, SCSP_SYSSTATUS, SCSP_UpdateAppMatchTable, SCSP_WriteErrorEvent, SCSP_WriteEvent.
The SCSP_OPSROLE requires db_datareader and db_datawriter on the SCSPDB database.
Traditionally SQL authentication is used to connect to the SQL server, in that case the DCS installation creates an SQL user named SCSP_OPS and populates that user in the server.xml file on the DCS manager that contains the connection details for the DCS manager to the SQL server. If however you choose to use windows authentication, the SCSP_OPS user ('the operational user') is probably named differently and is a windows domain user, what ever the authentication type and user name is used, here are the required permissions and privileges for this user.
The operational user requires CONNECT and VIEW DATABASE type of permission on the SCSPDB and needs to be assigned the SCSP_OPSROLE role.
The permissions for all of the above functions, stored procedures and views are required even after installation/upgrade for normal day-to-day function.