How to give access to DBCC showfilestats for non-DBO users

[Rajesh Ramadas]

When I removed DBO access from some users and instead given db_datareader and
db_datawriter, the non-dbo users are not able to see the properties of the
database in SQL Server Management Studio. They are getting the following
error:

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

User 'public' does not have permission to run DBCC showfilestats for
database 'bloomberg_bond'. (Microsoft SQL Server, Error: 7983)

[Erland Sommarskog]

I was able to reproduce this. As the information is available to
non-priv users I started to produce a bug report. However, before
I had completed it occurred to me that I should test on SQL 2008 as
well. And lo and behold! This has been addressed in SQL 2008 so that
you need to be db_owner to run DBCC SHOWFILESTATS.

I'm afraid that you will have to live with this as long as you are on
SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx