IBM Support

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?

Technote (FAQ)


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

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 the following data sets:
· Server details data set
· Database Details data set
· Job Details data set
· Database Mirroring data set
· Server Summary data set
· Job Summary data set

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 all the system databases and the user-defined databases which you want to monitor:
· public
· db_owner

For the msdb database, select the following additional check boxes:
· db_datareader
· SQLAgentReaderRole
· SQLAgentUserRole

3. Open the Securables page, and then select the following check boxes for the server instance that you are monitoring:
· View any database
· View any definition
· View server state

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.

Product Alias/Synonym

SQL MSSQL permissions 5724B96MO ITM Ticoli Monitoring

Document information

More support for: Tivoli Composite Application Manager for Microsoft Applications
Microsoft SQL Server Agent - 5724B96MO

Software version: 6.3.1, 6.3.1.1, 6.3.1.2, 6.3.1.8, 6.3.1.10

Operating system(s): Windows

Reference #: 2005767

Modified date: 19 February 2018