IBM Tivoli Composite Application Manager for Microsoft Applications, Version 6.3.1

Permissions required for configuring the monitoring agent

The user ID on the SQL Server must be granted specific permissions for configuring the monitoring agent.

If the Microsoft SQL Server agent is configured for Windows authentication, the agent uses the run-as user ID to access the Microsoft SQL Server. Otherwise, the agent uses the configured SQL Server user ID. Further reference in this section to Microsoft SQL Server user ID or SQL Server ID refers to either the run-as user ID or the Microsoft SQL Server user ID.

The Microsoft SQL Server user ID must have access to the Microsoft SQL Server agent. The procedure described in this section includes creating a Microsoft SQL Server user ID and granting permission to the new user ID, which is the minimum authority required.

The SQL Server ID used to configure this monitoring agent must have the following SQL Server authorities:

Required authorization roles
- Database roles: Public access is required for each database that is being monitored.
- Server roles: No special Server Role is required.
Optional authorization roles
- Each Take Action command has a separate set of authorization roles that are required for the SQL Server credentials to pass to the Take Action command. For more information about these authorization roles, see the "Take Action commands reference" topic in the Microsoft SQL Server agent: Reference.

The monitored attributes in Table 1 require additional authorization for the SQL Server ID used to configure the Microsoft SQL Server agent. If you want to monitor any of these attributes, configure the SQL Server ID used by the monitoring agent with the corresponding authorization.

Table 1. Additional authorization
Attribute group	Navigation item, workspace, view	Situation	Authorization
MS SQL Job Detail	Navigator item: Jobs	None predefined	Server Role: Server AdministratorsThis authorization enables data collection for all SQL Server jobs. —OR— Database Role (msdb database): publicAn SQL Server Agent proxy account must be defined. The agent can collect job data only on the jobs that this SQL Server ID owns. —OR— Database Role (msdb database): SQLAgentUserRole (SQL Server 2005 only)The agent can collect job data only on the jobs that this SQL Server ID owns. —OR— Database Role (msdb database): SQLAgentReaderRole (SQL Server 2005 only)The agent can collect job data on all SQL Server jobs. —OR— Database Role (msdb database): SQLAgentOperatorRole (SQL Server 2005 and 2008)The agent can collect job data on all SQL Server jobs.
	Workspace: Job Detail
	View: Job Status, Job Detail
MS SQL Job Summary	Navigator item: Jobs
	Workspace: Job Summary
	View: Running Job Current Interval, Job Summary
MS SQL Lock Detail MS SQL Lock Summary	Navigator item: Server Locking	None predefined	VIEW SERVER STATE permission is required on the SQL Server
	Workspace: Server Locking, Lock Detail
	View: Log Detail, Lock Detail
MS SQL Server Detail	Navigator item: Servers	MS_SQL_Proc_Buffs _Active_Warn MS_SQL_Proc_Buffs _Active_Crit MS_SQL_Proc_Buffs _Used_Warn MS_SQL_Proc_Buffs _Used_Crit MS_SQL_Proc_Cache _Active_Warn MS_SQL_Proc_Cache _Active_Crit MS_SQL_Proc_Cache _Used_Warn MS_SQL_Proc_Cache _Used_Crit	Database Role: db_owner (on default database associated with the SQL Server ID) —OR— Server Role: System Administrator
	Workspace: Server Detail
	View: Server Detail-Status, Server Detail-Statistics
MS SQL Server Summary	Navigator item: Servers	None predefined	VIEW SERVER STATE permission is required on the SQL Server
	Workspace: Server Summary
	View: CPU Utilization, Server Summary
MS SQL Statistics Summary	Navigator item: Servers	MS_SQL_Client_Cnt _Pct_Used_Crit MS_SQL_Client_Cnt _Pct_Used_Warn MS_SQL_IOERR _Startup_Warning MS_SQL_IOError _Curintvl_Warning MS_SQL_LogonPct _Crit MS_SQL_LogonPct _Warning MS_SQL_Network _Read_Rate_Crit MS_SQL_Network _Read_Rate_Warn MS_SQL_Network _Write_Rate_Crit MS_SQL_Network _Write_Rate_Warn MS_SQL_Pct _IO_Warning	VIEW SERVER STATE permission is required on the SQL Server
	Workspace: Server Statistics
	View: Statistics Summary Chart view, IO Summary, Statistic Summary table view
MS SQL Table Detail	Navigator item: Databases	MS_SQL_Fragmentatio n_Warn MS_SQL_Fragmentation _Crit MS_SQL_Opt_Stats _Age_Warn MS_SQL_Opt_Stats _Age_Crit	Database Role: db_owner (for each database) —OR— Server Role: System Administrator —OR— VIEW DATABASE STATE and VIEW SERVER STATE permissions are required on the SQL Server
	Workspace: Table Detail
	View: Table Detail, Fragmentation (greater than 30%) by Index Name
MS SQL Audit Details	Navigator item: Servers	None predefined.	Server Role: Server Administrator —OR— CONTROL SERVER permission is required on the SQL Server
	Workspace: Audit Details
	View: Audit Details
MS SQL Database Detail	Navigator item: Databases	None predefined	Server Role: Server Administrator —OR— CONTROL SERVER permission is required on the SQL Server
	Workspace: Databases Information
	View Database Detail
MS SQL Filegroup Detail	Navigator item: Databases	None predefined	Server Role: Server Administrator —OR— CONTROL SERVER permission is required on the SQL Server
	Workspace: Filegroup Detail
	View: Filegroup Detail
MS SQL Device Detail	Navigator item: Databases	None predefined	Server Role: Server Administrator —OR— CONTROL SERVER permission is required on the SQL Server
	Workspace: Device Detail
	View: Device Detail

Feedback