IBM SQL Agent : Creating an SQL Server user and granting permissions to the user for monitoring the Microsoft SQL Server with the Microsoft SQL Server agent?

Question
How to create an SQL Server user and grant permissions to the user for monitoring the Microsoft SQL Server with the Microsoft SQL Server agent?

Answer
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

Install the Microsoft SQL Server agent. To create a user and grant permissions to the user, you must have Sysdamin authorization role.

Procedure

To create a user, complete the following steps:
1. In the SQL Server Management Studio, open Object Explorer.
2. Click Server_instance_name > Security > Logins.
3. Right-click Logins and select New Login.
4. On the General page, in the Login name field, type the name of user.
Important: Specify a windows user in case going for Windows authentication in step 5.
5. Select Windows authentication or SQL Server authentication.
Important: If you select SQL authentication, you must set the password for the user.
6. Depending on the role and permissions that you want to assign to this 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 that are mentioned in Granting minimum required permissions for data collection.
Important: By default, the public role is assigned to the new login ID.
7. Click OK.

Results

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

Granting minimum required permissions for data collection

Apart from the default public role, you can assign the sysadmin role to a user so that the agent can collect data for all the agent attributes. If you don’t want to assign the sysadmin role, you can grant some minimum required permissions to the user so that the agent can collect data for some data sets.

About this task

The default public role is sufficient to collect data for most data sets. However, you must grant minimum required permissions to the user if you want the agent to collect data for all the attribute groups having SQL query as data source:

Procedure
To grant minimum required permissions to the user, complete these steps:

1. Open the Server Roles page and verify that the public check box is selected.

2. Open the User Mapping page, and then select the following check boxes for master databases:

· public
· db_owner

3. For the msdb database, select the following check boxes in User Mapping page:

6. If you are using customized SQL query, grant SELECT permission on the objects mentioned in the customized SQL query.

Note: To collect mirror state and mirror role of Mirrored database in MS SQL Database Mirroring, sysadmin fixed server role is required. With above permissions, data for only the Principal database will be shown. It is also applicable for mirror role in MS SQL Database Detail.

Granting permission to perflib registry key for collecting data for few attribute groups

Before you begin

This permission must be granted to the windows user with which agent services are configured. There are many attribute groups that will be 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, etc.

Procedure
1. Stop the SQL agent on the problematic server.
2. To open the Registry Editor, click Start > Run > Type regedit.exe > Press Enter.
3. Go to following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib
4. Right-click the Perflib key, then click Permissions...
5. Click Add…, and add the windows user name with which agent is installed and configured and click OK.
6. Click the user that you added.

There are three options of access levels [Full Control, Read, and Special permissions].
Full Control - Permission to open, edit, and take ownership of the key.
Read - Permission to read the key contents, but not edit any changes.
Special - Permission to change the owner of key and selectively grant permission to its subkeys.

7. Allow read access to the user by selecting the check box.
8. Click Apply, and then OK.
9. Start the agent on the problematic server and check the data.