Creating a user and granting permissions
On the Microsoft SQL Server, you must create a user under which the agent runs, and grant permissions to the user for monitoring Microsoft SQL Server. The process of granting permissions is the same for Microsoft SQL Server 2005, or later.
Before you begin
About this task
Checking the permissions of an existing SQL Server user
You can run the utility tool koqVerifyPerminssions.exe to check if an existing SQL Server user has sufficient permissions related to SQL Server databases.
About this task
- Permissions for server must include View server state, Control
server and View any definition.
These server level permissions are mandatory.
- For all system databases and the user-defined databases for monitoring, the database role
membership must include public and db_owner.The db_owner permission is required to collect data for the following data sets:
- Server Details data set
- Database Summary data set
- Database Details data set
- Database Mirroring data set
- Server Summary data set
- Job Summary data set
- Job Detail data set
- Availability Replicas Details In Cluster data set
- For msdb database, the database role membership must include public, db_owner, db_datareader, SQLAgentReaderRole and SQLAgentUserRole. These permissions are required for Job Details data set.
Procedure
Results
koqVerifyPermissions_log
at
the following directory:- For 64-bits agents, Agent_home\TMAITM6_x64\logs
- For 32-bits agents, Agent_home \TMAITM6\logs
Creating a SQL Server user ID with Windows authentication
Create a new user with the Windows authentication and assign the required roles and permissions to the user.
Procedure
To create a user, complete the following steps:
Results
Creating a SQL Server user ID with SQL Server authentication
Create a new user with the SQL Server authentication and assign the required roles and permissions to the user.
Procedure
To create a user, complete the following steps:
Results
Granting minimum permissions for data collection
Apart from the default public role, you can assign the sysadmin role to a user or grant the minimum permissions to a user so that the agent can collect data for data sets.
About this task
Procedure
What to do next
Granting permission to the Perflib registry key for collecting data for few data sets
To collect data for few date sets, you need to grant users read access to the Perflib registry key.
About this task
You need to grant this permission to the Windows user with which agent services are configured. There are many data sets that are affected in absence of Perflib permissions like MS SQL Database Detail, MS SQL Memory Manager, MS SQL Lock Resource Type Summary, MS SQL Job Summary, MS SQL Server Transactions Summary, MS SQL Server Summary, and so on.
Procedure
To grant permission to the Perflib registry key, complete these steps:
- To open Registry Editor, click Enter. , and press
-
Go to the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib
registry key. - Right-click the Perflib key, and click Permissions.
- Click Add, enter the windows user name with which the agent is installed and configured, and then click OK.
- Click the user that you added.
- Allow read access to the user by selecting the check box.
- Click Apply, and then click OK.