How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied?

I have a table SuperSecretData which is currently setup to deny SELECT to everyone in the company by the standard Active Directory account "Domain Users". But a couple of specific individual users do need to be able to SELECT from it.
What's the proper way to achieve this?

DENY overrides GRANT, and an inherited DENY even overrides a direct GRANT. And a DENY at the schema or database level overrides a GRANT at the object level.

So you won't be able to directly override the DENY.

You can create a view, synonym, or stored procedure owned by the same user as SuperSecretData and grant them access to that.


is there a better way to GRANT SELECT to almost all entities on my SQL server to "Domain Users" except for a couple of tables that I'd only want some specific users to have SELECT permissions on?

Yes. Never use the built-in roles, and always use schema-level grants. Then you can create separate schemas for additional security. Eg

grant select on schema::dbo to ...

instead of a built-in role

alter role db_datareader add . . .

or a database-level grant

grant select to ...

StackExchange Database Administrators Q#250039, answer score: 7