Audit queries by permissions required

Hamilton, Chuck 1 Reputation point
2023-05-03T15:30:03.4466667+00:00

Is there a way to "audit" queries by the permissions they require to run? For example I want to capture every query that requires the "VIEW SERVER STATE" permission to run.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. LiHongMSFT-4306 24,931 Reputation points
    2023-05-04T06:01:53.75+00:00

    Hi @Hamilton, Chuck

    I want to capture every query that requires the "VIEW SERVER STATE" permission to run.

    Permissions in the Database Engine are managed at the server level assigned to logins and server roles, and at the database level assigned to database users and database roles.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Erland Sommarskog 104.2K Reputation points MVP
    2023-05-04T20:59:51.59+00:00

    I don't think there is anything built-in for this. You could set up a trace/X-event statement that is filtered for things that requires VIEW SERVER STATE, but that would take some time to get right. And technically not entirely possible. Anyone can query sys.dm_exec_sessions, but if you don't have VIEW SERVER STATE, you only see your own process.

    0 comments No comments