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