I recently saw an interesting blog article by Paul Randal about the performance overhead of querying the sys.dm_db_index_physical_stats.

So I was thinking, would it be possible to let non-sysadmin users query DMVs on a SQL server but stop them querying this I/O intensive DMV ?

Yes it is, here’s how…

1. Create a new login for test purposes, with permissions to access AdventureWorks database only …

CREATE LOGIN [test] WITH PASSWORD='xxxx', DEFAULT_DATABASE=[AdventureWorks]
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
GO

2.login as user test and issue command

SELECT  * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')

gets error :- 

Msg 297, Level 16, State 12, Line 1

The user does not have permission to perform this action.

3.As a sysadmin, issue command :-

USE AdventureWorks

GRANT VIEW DATABASE STATE TO [test] or GRANT VIEW SERVER STATE TO [test] if all databases can be queried via DMV.

4. Try again as user test to issue command

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks '),NULL,NULL,NULL,'DETAILED')

-- now produces valid results from the DMV..

5 now create the test user in master database, public role only

USE master

CREATE USER [test] FOR LOGIN [test]

6 issue command :-

USE master

DENY SELECT ON sys.dm_db_index_physical_stats TO [test]

7 Now go back to AdventureWorks using test login and try

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks’),NULL,NULL,NULL,’DETAILED')

Now gets error...

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'dm_db_index_physical_stats', database 'mssqlsystemresource', schema 'sys'.

but the user is still able to query all other non-IO-intensive DMVs.

If the user attempts to view the index physical stats via a builtin management studio report  – see recent blog post by Pinal Dave they get an error also