Creating a user and granting permissions

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

1. 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.
2. Run the koqVerifyPerminssions.exe by providing the parameters:

   koqVerifyPermissions.exe -S Instance_name [-U Username -P Password] 
   [-V Verify_User]

   Where:

   • Instance_name is the SQL Server instance name.
   • Username is the user name that is used to login to the SQL server.
   • Password is the password of the user. This parameter is required if username is provided.
   • Verify_User is the user for which permissions are verified. If this is not provided, then permissions are checked for -U user. If -V and -U both are not provided, then permissions are verified for current windows user running this command.

   Note: If the username and the password are not provided, then the user running this command is used for login purpose. Check the command help koqVerifyPermissions.exe -H for more information.

Create a new user with the Windows authentication and assign the required roles and permissions to the user.

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 a Windows user.
5. Select Windows authentication.
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 permissions for data collection.
   Important: By default, the public role is assigned to the new login ID.
7. Click OK.

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

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 for a new user.
5. Select SQL Server authentication.
6. In the Password field, type a password for the user.
7. In the Confirm Password field, retype the password that you entered in the Password field.
8. 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 permissions for data collection.
   Important: By default, the public role is assigned to the new login ID.
9. Click OK.

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.

• To grant the minimum permissions to the user via the user interface, 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 following checkbox for master database.
     • public
     • db_owner
  3. Additionally, on the User Mapping page, 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:

     • public
     • db_owner
     • db_datareader
     • SQLAgentReaderRole
     • SQLAgentUserRole
  4. Open the Securables page, and then select the following check boxes for the server instance that you are monitoring:
     • view any definition
     • view server state
     • control server
• To grant the minimum permissions to the user by using the utility tool permissions.cmd, complete the following:
  1. Launch 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.
     • 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 entry in the exclude_database.txt file:

     MyDatabase1@MyDatabase2

  2. Double click permissions.cmd to launch the utility tool.
  3. Enter the intended parameter values when prompted:

     Table 1. Parameters

     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 will be altered.
     Permissions options: 1 Grant db_owner permission 2 Grant db_datareader, SQLAgentReaderRole and SQLAgentUserRole permissions 3 Grant all required permissions	Enter 1 or 2 or 3 according to your requirement.
     The user to grant permissions: 1 The user who is currently logon to the system 2 Another user	Enter 1 or 2. If 2 is select, enter the target user name when prompted. Note: The users must have access to grant permissions to other users.

To collect data for few date sets, you need to grant users read access to the Perflib registry key.

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

1. To open Registry Editor, click Start > Run > Regedit.exe, and press Enter.
2. Go to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib registry key.
3. Right-click the Perflib key, and click Permissions.
4. Click Add, enter the windows user name with which the agent is installed and configured, and then click OK.
5. Click the user that you added.
6. Allow read access to the user by selecting the check box.
7. Click Apply, and then click OK.