KB: Required SQL Server permissions

For SQL Data Profiler, the queries require access to the system views INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES. For every database that a connected account wants to profile, the account needs permissions granted to access these system views. By default, sysadmin has these permissions. That is, VIEW SERVER STATE permission is required to retrieve Profile results.

To issue grants using a database administrator account with SYSADMIN privileges:

  1. To grant VIEW SERVER STATE for a specific database, in SQL Server Management Studio:
    1. Expand Databases.
    2. Right-click on the desired database and select Properties.
    3. Select Permissions.
    4. Select View server permissions.
    5. In the Logins or Roles list box, select the user(s) to whom you want to grant the permission.
    6. In the Explicit permissions for user table, for View server state permission, select the Grant check box.

  2. To grant VIEW SERVER STATE for all databases, in SQL Server Management Studio:
    1. Right-click on the name of the SQL Server instance and select Properties.
    2. Select Permissions.
    3. Select View server permissions.
    4. In the Logins or Roles list box, select the user(s) to whom you want to grant the permission.
    5. In the Explicit permissions for user table, for View server state permission, select the Grant check box.

  3. To grant VIEW SERVER STATE for a specific database, run the T-SQL commands:
    USE [database]
    GO
    GRANT VIEW SERVER STATE TO [user]

  4. To grant VIEW SERVER STATE for all databases, run the T-SQL commands:
    USE master
    GO
    GRANT VIEW SERVER STATE TO [user]

Please also refer to the Microsoft document "GRANT Server Permissions (Transact-SQL)".