I have a user account on a Sql Server 2008 R2 database, that is member of the db_datareader database role and has no further permissions. Why is this user not allowed to execute the following SELECT statement?

SELECT * FROM sys.dm_db_partition_stats

And which permissions do I have to set to allow him executing this query but still prevent him from doing anything harmful to the database?

2 Answers

You need to grant VIEW DATABASE STATE for this:

grant VIEW DATABASE STATE to [<YourUser>]

If only there was some way to search the web that would lead you to the documentation for the product:

sys.dm_db_partition_stats:

Requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view. For more information about permissions on dynamic management views, see Dynamic Management Views and Functions (Transact-SQL).

and db_datareader (okay, I'll admit this one is a little trickier to find):

Members of the db_datareader fixed database role can read all data from all user tables.

My emphasis

2