How To Authorize non-admin Logins To Read DMWs and DMFs

 

Some logins may want to see queries that requested from the database.

They can see these queries from SQL Server  DMWs(Dynamic Management View) and DMFs(Dynamic Management Functions).

However, to be able to query dmws and dmfs, the login must be granted VIEW_SERVER_STATE permission at the server level.

You can give this permission with the below script.

 

When we give this permission to Login, it will be able to see the queries requested from every databases.

For information security, it is not appropriate for anyone to see queries requested from another database.

If you want to see only queries requested from your own databases without having the VIEW_SERVER STATE privilege, you can read my article named “How To Give Restrict Access To dmw and dmf To Logins Without Giving View_Server_State Permission“.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *