Sybase permissions for DPA monitoring

10/22/2018 1:00 PM

9/9/2019 1:14 PM

DPA creates a login with SA_ROLE and MON_ROLE privileges or uses an existing login with those privileges. DPA uses that login to monitor a Sybase instance and perform query plans for the SQL statements that it captures. DPA uses this login to view four MDA tables (monProcess, monProcessProcedures,monProcessSQLText, and monProcessLookup) and records information for each active session to a database repository. The query plans are performed on the monitored database, then stored for historical access in the repository.

DPA configures the Sybase instance with the following:

Minimum Version Driver Database	12.5.0.3 JDBC: iTDS Driver Register each instance
	create login for DB user in "master" create monitoring user in "master" grant sa_role grant mon_role sp_configure enable cis, 1
Versions 12 to 15.0	sp_addserver loopback,null,@@servername
15.0 and below use loopback	create existing table monProcess
15.0 and below use loopback	create existing table monProcessProcedures (12.5.3+ has LineNumber column)
15.0 and below use loopback	create existing table monProcessLookup
15.0 and below use loopback	create existing table monProcessSQLText (12.5.3+ has ServerUserID column)
15.0 and below use loopback	create existing table monWaitEventInfo
	grant select on monProcess to mon_role grant select on monProcessProcedures to mon_role grant select on monProcessLookup to mon_role grant select on monProcessSQLText to mon_role grant select on monWaitEventInfo to mon_role sp_configure enable monitoring,1 sp_configure wait event timing,1 sp_configure SQL batch capture,1 sp_configure enable cis,1 set cis_rpc_handling on
if < 4000	sp_configure max SQL text monitored,4000

The Sybase instance must be restarted if max SQL text monitored was not initially 4000.

Database Performance Analyzer

Configure DPA to not require SA_ROLE 

DPA requires the SA_ROLE permission during the wizard setup for monitoring and for collections of historical query plans . If you do not need the historical query plan feature, you can remove the SA_ROLE once monitoring is set up. In some versions of Sybase, you may need to explicitly grant select access on the monProcess, monSysStatement, monTableColumns and monProcessSQLText tables.

To turn off historical query plan collections so DPA will not log errors:

1. Click Options > Administration > Advanced Options.
2. Click the PLAN_COLLECTION_ENABLED system option.
3. Change the New Value to False, and click Update.