Grant access to DMV's without giving VIEW SERVER STATE permission

As Dan says, this is possible. You wrap the query you want to the user to be able to run in a stored procedure, which you for simplicity you can place in the master database.

Then you apply this recipe:

1. Create a certificate.
2. Sign the procedure with the certificate.
3. Create a login from that certificate.
4. Grant that login VIEW SERVER STATE.

The login is not a normal login, but a special type which exists only to connect certificate and permission. It is not possible to log in with this login.

I describe this technique in a lot more detail in my article: https://www.sommarskog.se/grantperm.html. And, yes, that is a bit to read, although you only need to read first five chapters. But it's time well-spent, because it is a very tool to have at hand.

Hi,

From the documentation:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

The answer to your question depend on the type of DMV you want to give permission to.

If you will try to GRAND permission on Server-scoped DMV (for example try sys.dm_os_wait_stats) to a USER in a database, then you will; get the error: "Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master."