Creating a user and granting permissions

On the Microsoft SQL Server, you must create a user for the agent, 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

Install the Microsoft SQL Server agent. To create a user and grant permissions to the user, you must be a database administrator with the sysdamin authorization role.

About this task

Use the following procedure to determine if an existing SQL Server user has sufficient permissions to monitor Microsoft SQL Server:

Checking the permissions of an existing SQL Server user

Use one of the following procedures to create a user:

Creating an SQL Server user ID with Windows authentication
Creating an SQL Server user ID with SQL Server authentication

Use the following procedure to grant permissions:

Granting minimum permissions for data collection
Granting permission to the Perflib registry key for collecting data for few data sets

Checking the permissions of an existing SQL Server user

You can run the utility tool koqVerifyPermissions.exe to check if an existing SQL Server user has sufficient permissions related to SQL Server databases.

The utility tool koqVerifyPermissions.exe returns the message PASS if the user has sysadmin role or the minimum required permissions. The detailed checking result is logged in koqVerifyPermissions_log.

The following lists the minimum permissions:

Permissions for server must include View server state, View any database 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 Details data set
- Database Mirroring data set
- Server Summary data set
- Job Summary data set
For msdb database, the database role membership must include db_datareader, SQLAgentReaderRole and SQLAgentUserRole. These permissions are required for Job Details data set.

Procedure to check the permissions of an existing SQL Server user

Launch the command prompt and change to the following utility directory.
- For 64-bits agents, Agent_home\TMAITM6_x64
- For 32-bits agents, Agent_home\TMAITM6
where Agent_home is the agent installation directory.
Run the koqVerifyPermissions.exe by providing the parameters:
```
koqVerifyPermissions.exe -S Instance_name -U Username -P Password
```
Where:
- Instance_name is the SQL Server instance name.
- Username is the user name that is verified by the utility tool.
- Password is the password of the user. This parameter is required if username is provided.
Note: If the username and the password are not provided, the default user that is logon to the system is used. Example: NT AUTHORITY\SYSTEM.

The detailed checking result is available in koqVerifyPermissions_log at the following directory:

For 64-bits agents, Agent_home\TMAITM6_x64\logs
For 32-bits agents, Agent_home\TMAITM6\logs

Where Agent_home is the agent installation directory.

Creating an 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 an SQL Server user ID with Windows authentication

To create a user, complete the following steps:

In the SQL Server Management Studio, open Object Explorer.
Click Server_instance_name > Security > Logins.
Right-click Logins and select New Login.
On the General page, in the Login name field, enter the name of a Windows user.
Select Windows authentication.
Depending on the role and permissions that you want to assign to the user, complete one of the following tasks:
- On the Server Roles page, assign the sysadmin role to the new login ID.
- If you do not want to assign the sysadmin role to the user, grant minimum permissions to the user by completing the steps in Granting minimum permissions for data collection.
Important: By default, the public role is assigned to the new login ID.
Click OK.

A user is created with the default public role and the permissions that you assigned, and is displayed in the Logins list.

Creating an SQL Server user ID with SQL Server authentication

Create a user with the SQL Server authentication and assign the required roles and permissions to the user.

Procedure to create an SQL Server user ID with SQL Server authentication

To create a user, complete the following steps:

In the SQL Server Management Studio, open Object Explorer.
Click Server_instance_name > Security > Logins.
Right-click Logins and select New Login.
On the General page, in the Login name field, enter the name for a new user.
Select SQL Server authentication.
In the Password field, enter a password for the user.
In the Confirm Password field, retype the password that you entered in the Password field.
Depending on the role and permissions that you want to assign to the user, complete one of the following tasks:
- On the Server Roles page, assign the sysadmin role to the new login ID.
- If you do not want to assign the sysadmin role to the user, grant minimum permissions to the user by completing the steps in Granting minimum permissions for data collection.
Important: By default, the public role is assigned to the new login ID.
Click OK.

A user is created with the default public role and the permissions that you assigned, and is displayed in the Logins list.

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.

You can grant the permissions by using the user interface or the utility tool permissions.cmd.

Procedure to grant minimum permissions for data collection

To grant the minimum permissions to the user by using the user interface, complete the following steps:

Open the Server Roles page and verify that the public check box is selected.
Open the User Mapping page, and then select the following check boxes for all the system databases and the user-defined databases that you want to monitor:
- public
- db_owner
For the msdb database, select the following check boxes:
- db_datareader
- SQLAgentReaderRole
- SQLAgentUserRole
Open the Securables page, and select the following check boxes for the server instance that you are monitoring:
- view database
- view definition
- view server state

To grant the minimum permissions to the user by using the utility tool permissions.cmd, complete the following steps:

Start the Windows Explorer and browse to the utility tool directory Agent_grant_perm_dir:
- For 64-bits agent, Agent_grant_perm_dir is Agent_home\TMAITM6_x64\scripts\KOQ\GrantPermission.
- For 32-bits agent, Agent_grant_perm_dir is Agent_home\TMAITM6\scripts\KOQ\GrantPermission.
- The Agent_home is the agent installation directory.
Attention: The utility tool permissions.cmd grants db_owner on all databases by default. To exclude certain databases, you must add the database names in the Agent_grant_perm_dir\exclude_database.txt file. The database names must be separated by the symbol alias @.

Tip: For example, you want to exclude the databases MyDatabase1 and MyDatabase2, add the following entries in the exclude_database.txt file:
```
MyDatabase1@MyDatabase2
```
Double-click permissions.cmd to start the utility tool.
Enter the intended parameter values when prompted:

Parameters	Description
SQL Server name or SQL Server instance name	Enter the target SQL Server name or the target SQL Server instance name that you want to grant permissions to the user.
The existing SQL Server user's logon name	Enter the user name whose permissions to be altered.
Permissions options: Grant db_owner permission Grant db_datareader, SQLAgentReaderRole and SQLAgentUserRole permissions Grant all required permissions	Enter 1 or 2 or 3 according to your requirement.
The user to grant permissions: The user who is logon to the system The user who is logon to the system	Enter 1 or 2. If 2 is selected, enter the target user name when prompted. Note: The users must have access to grant permissions to other users.

What to do next

Configure the agent.

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.

You need to grant the permission to the Windows user with which agent services are configured. There are many data sets that are affected in absence of Perflib permissions. The affected data sets are 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 others.

Procedure for granting permission to the Perflib registry key

To grant permission to the Perflib registry key, complete the following steps:

To open Registry Editor, click Start > Run > Regedit.exe, and press Enter.
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.