Share via

VIEW SERVER STATE vs DB role for DMV on master

Alen Cappelletti 1,047 Reputation points
2024-04-24T08:29:59.1+00:00

Hi,
I have a logging procedure that reads from the two tables on master:

  • sys.dm_exec_connections
  • sys.dm_exec_sessions

I had to assign permissions to a group for correct execution:

  • GRANT VIEW SERVER STATE TO [domain\mygroup];

but I didn't want to give them too much.

I also tried with a db role on master, but seems not enough, can you confirm that?

USE master;
GO
CREATE ROLE sp_log_usage;
GRANT SELECT ON sys.dm_exec_connections TO sp_log_usage;
GRANT SELECT ON sys.dm_exec_sessions TO sp_log_usage;
ALTER ROLE sp_log_usage ADD MEMBER [domain\mygroup];

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission

Thanks ALEN

SQL Server | Other
0 comments
Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2024-04-24T21:28:20.9333333+00:00

    Rather than granting the permission to the group, grant it to the procedure. Or more precisely, sign the procedure with a certificate, create a login from that certificate and grant that login the permission needed. Which on SQL 2022 and SQL MI is VIEW SERVER PERFORMANCE STATE.

    I describe the technique with certificate signing in a lot more detail in an article on my web site: Packaging Permissions in Stored Procedures

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-25T03:04:00.2566667+00:00

    Hi @Alen Cappelletti,

    Thanks for your information.

    I agree with Erland Sommarskog. Sign in with a certificate based on a login with the needed permissions is the most secure way to wrap the DMV query in a proc. I've checked out the article Erland provided; it is a good tool to have at hand, so, I think it's worth for us to spend time reading the article.

    In addition, for the VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and SQL MI, this article tells you the detailed information, hope this can help you well.

    Best regards,

    Lucy Chen

    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.

    https://docs.microsoft.com/en-us/answers/support/email-notifications

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.