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.

Amour

Have you tried select count(1) ... ? – Apus

Does select count(name) work? – Hypha

If you write COUNT(*), extend to all columns of your table. Try with COUNT(name) or count(1) – Softball

@Apus yes select count(1) ... also does not work. – Amour

@a_horse_with_no_name @JoeTaras SELECT COUNT(name) will not work since name might be null and so the result is incorrect. I know the example does not make perfect sense but basically I have a situation where I do not have a non null column available. – Amour

@Amour have you try to select count(nameSub) from (select ISNULL(name, ' ') as 'nameSub' from product) as TabSub – Hammad

In the ANSI/iSO SQL-92 Standards and up, a COUNT(*) means "the cardinality of a table expression" ...The table expression can be a table, derived table, (corelated) subquery, VIEW or a CTE... I wonder what happens if you add a IDENTITY(1, 1) PRIMARY KEY column aswell without that you also dont really have a table...The idea behide that the optimizer should have statistics for the cardinality for that table then.. – Cyanotype

The best solution is probably to 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. COUNT(*) is probably not the only thing that will behave inconveniently when you start denying access to individual columns. – Inheritrix

@JeroenMostert you should post that as an answer to the question "And what would be a workaround to allow the Intern user to still get an accurate count of Product?" – Vanny

@JeroenMostert You are correct that creating a View would be the best solution, I was most interested how the Intern would work around this and he presumably can not create views. – Amour

@JeroenMostert, I took the liberty to incorporate your excellent suggestion into my answer, in order to make the answer better suited for future users. Hope that's okay. – Theotokos

Expanding on COUNT(name) example... does SELECT COUNT(COALESCE(name, 'foo')) work? – Lepton

@pascalsanchez your solutions to convert the null name to something else work. The only part that is not optimal about these solutions is the slightly worse performance. – Amour

@SalmanA your idea is similar to pascalsanchez s solution and it will work. Just with a slight performance penalty. – Amour

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.

Theotokos

I tried your second solution and it actually works (even without index). That is really quite surprising, it seems like adding the WHERE clause changes the meaning of the count(*) somehow?? – Amour

I tried SELECT COUNT(1) FROM dbo.Product WHERE P.name = P.name OR 1=1 and it actually works and is correct. – Amour