Adaptive Server has database privileges that restrict the access and authority of each user. Although it is not required that the user logon have all of these privileges, certain functionality will not be available to the user without specific privileges.
When you logon to Quest SQL Optimizer for SAP ASE, it checks to see if the logon satisfies the privileges below.
To see if your logon satisfies the privilege requirements for each module
Determine which of the following database setup and user privileges are needed for your environment and follow the steps under the following sections.
SELECT Permission on database objects
sa_role
CREATE PROCEDURE
Adaptive Server Monitor Tables
mon_role
Adaptive Server Configuration Parameters
If the user is not the owner of the database objects, the user logon account must have the SELECT permission on the objects. If the user logon account does not have access to database objects in Adaptive Server, then these objects cannot be used or viewed in Quest SQL Optimizer for SAP ASE.
The sa_role privilege is needed for a few functions in Quest SQL Optimizer for SAP ASE. If the logon does not have this privilege, most of the functionality of Quest SQL Optimizer for SAP ASE is still available.
Assign 'sa_role' to the logins needing the following functionality:
Use the SQL Collector for Monitor Server module.
Abort Run Result function for SQL statements that return data.
Retrieve Trace On (dbcc) information
Grant sa_role to the logon using the following command in the SQL Worksheet or ISQL:
sp_role 'grant', sa_role, logon_name
The CREATE PROCEDURE privilege is required to optimize a SQL statement that runs inside a cursor. A procedure is created when the SQL for Cursor checkbox is selected in the SQL Optimizer window (in the SQL Optimizer module) or when the SQL Scanner finds a SQL statement that is executed within a cursor declaration. The stored procedure is created to retrieve the query plan and run time. The stored procedure is executed and then dropped.
If the user logon does not have sa_role which includes this privilege, then grant this privilege to the logon using the following command in the SQL Worksheet or ISQL:
grant create procedure to logon_name
The SQL Inspector and Performance Monitor require installation of the Adaptive Server monitoring table. Later versions of Adaptive Server create the monitoring tables are by default when you install Adaptive Server. So depending on the version of Adaptive Server you are running, you may find these tables already installed.
The tables are creating using the installmontables script which is included in the Adaptive Server install and is located in the $SYBASE/ASE-version/scripts directory (%SYBASE%/ASE-version/scripts for NT). This script requires that a server named "loopback" be included in sysservers.
If the monitoring tables are not installed yet in your Adaptive Server, follow these steps to for installing the tables.
Create a loopback server entry either using ISQL or SQL Optimizer.
In SQL Optimizer:
Logon to Quest SQL Optimizer for SAP ASE with sa.
In the bottom left corner of the window, set the database to Master.
In the SQL Worksheet module, execute: sp_addserver 'loopback', null, server_name
In ISQL
Logon to Adaptive Server with sa.
- Execute: sp_addserver 'loopback', null, serve_name go
isql -Usa -P<sa password> -Sserver_name
-n -i%SYBASE%/%SYBASE_ASE%/scripts/installmontables
The mon_role privilege is needed to run the SQL Inspector and Performance Monitor modules. The sa logon does not have this role by default, so it needs to be added to the sa logon and any other user logon. Assign 'mon_role' to the logins using the SQL Worksheet module or ISQL with this command.
sp_role 'grant', mon_role, logon_name
The following modules require specific settings for Adaptive Server configuration parameters. (You can copy, paste and execute the following script into the SQL Worksheet module).
/* parameters for SQL Inspector and Performance Monitor modules using monitoring tables */
/* requires Adaptive Server 12.5.0.3 and later */
sp_configure "enable monitoring",1
go
sp_configure "sql text pipe active",1
go
sp_configure "sql text pipe max messages",1024
go
sp_configure "plan text pipe active",1
go
sp_configure "plan text pipe max messages",5000
go
sp_configure "statement pipe active",1
go
sp_configure "statement pipe max messages",5000
go
sp_configure "errorlog pipe active",1
go
sp_configure "errorlog pipe max messages",1024
go
sp_configure "deadlock pipe active",1
go
sp_configure "deadlock pipe max messages",1024
go
sp_configure "wait event timing",1
go
sp_configure "process wait events",1
go
sp_configure "object lockwait timing",1
go
sp_configure "SQL batch capture",1
go
sp_configure "statement statistics active",1
go
sp_configure "per object statistics active",1
go
sp_configure "max SQL text monitored",4096
go
/* parameters for SQL Inspector module using the QP Metrics */
/* requires Adaptive Server 15.0 and later */
sp_configure 'enable metrics capture',1
go
/* parameters for Abstract Plan Manager module */
/* requires Adaptive Server 12.0 and later */
sp_configure "abstract plan cache",0
go
sp_configure "abstract plan replace",0
go
/* parameters for retrieving Estimated I/O Cost for query plans */
sp_configure "allow resource limits",1
go
/* parameters for SQL Collector for Monitor Server module*/
sp_configure "event buffers per engine",2000
go
sp_configure "max SQL text monitored",4096
go
sp_configure "select on syscomments.text",1
go
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
