Why does COUNT(*) require SELECT permission on all table columns on SQL Server?

Question

I recently encountered the problem that COUNT(*) requires the user to have select permission on every single column of a table. Even though the spec of 'COUNT(*)' explicitly says that

it does not use information about any particular column.

It just returns the number of rows in the result.

So if you want to count the number of of rows in a table as a restricted user you get permission exceptions.

Here is an example:

CREATE TABLE [Product]
([name] nvarchar(100) null, [price] float)

CREATE USER Intern WITHOUT LOGIN;
DENY SELECT ON [Product] (price) TO Intern;

EXECUTE AS Intern;

-- Fails with "The SELECT permission was denied on the column 'price' of the object 'Product'"
SELECT COUNT(*) FROM [Product];

REVERT;

After some testing I found that even SELECT COUNT(1) FROM [Product] does not work.

Can someone explain what the reasoning behind this behaviour is? And what would be a workaround to allow the Intern user to still get an accurate count of Product.

Update: I would be most interested in workarounds that the Intern could use. So even though creating a View would be best practice for the admin, the Intern does not have this option.

Answer 1

I don't know the reasoning behind this behaviour, but there is a way around it:

SELECT  COUNT(1)
FROM    (
            SELECT  P.name
            FROM    dbo.Product AS P
        ) AS t;

Of course you need SELECT permission on Product.name, but I gather from your comments that shouldn't be an issue.

Addendum, because I agree it is unexpected behaviour. If you do the following, you are also permitted to execute the count (if you have an index on name as well as SELECT permissions on name):

SELECT  COUNT(1)
FROM    dbo.Product AS P
WHERE   P.name = P.name
    OR  P.name IS NULL 

---

The preceding works well from the point of view of the user who wants something they aren't allowed (Intern, in this case). From the point of view of the DBA, a better method exist to facilitate that user. (Copied from the comment of Jeroen Mostert above:)

You can create a view that explicitly excludes columns Intern should not see, and grant SELECT permission on that. This way, queries work as normal without having to introduce circuitous and unintuitive workarounds and you do not need separate DENY permissions per column either -- you don't have to grant SELECT permission on the base table in the first place.