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 ...