How to restirct access to DMVs and DMFs for users

We heavily use Dynamic Management Views and Functions for retrieving insight of inner operations of SQL Server and they are very useful. These DMVs and DMFs are based on Dynamic Management Objects (DMOs).

Generally, if user has permission on VIEW SERVER STATE and VIEW DATABASE STATE, user can execute almost all DMVs and DMFs. If you are a member of sysadmin server role, then you automatically get permission on VIEW SERVER STATE and if your login is a standard login and it has been added to one of the databases as a member of db_owner role, then you have VIEW DATABASE STATE permissions. These permission can be granted explicitly without adding logins or users to the specific roles.

However, if you need to restrict accessing specific DMVs or DMFs to users on one or more databases, there is an easy way of doing it. All we have to do is, restrict access on the specific DMVs and DMFs in the master database to user. It makes sure that user cannot access them regardless of the database context. 

Let's assume that user Jane should not be able to execute sys.dm_sql_referenced_entities DMF in any database. Here are the steps for doing it;

1. Create a user in master database for Jane's login.

2. Open properties of User Jane and click on Securables for listing required objects.

3. Click on Search for finding objects. Select All objects of the types... and click OK.

4. Select Table-valued function and click OK.

5. Select sys.dm_sql_referenced_entities DMF and set Deny permission on SELECT.

6. Click OK to save settings.

Now if you Jane tries to execute this DMF, she will get an error on it as below;