3

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?

Table Properties - Permissions

7

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 ... 
  • Yea I figured I might be a little out of luck going this route. But assuming I created a new view as you mentioned, wouldn't that bring me back to square one where anyone would be able to SELECT from it until I explicitly denied that permission? Also, if I structured our entities a little bit different such that SuperSecretData belonged to a schema called KeepOutPlebs, would I be able to explicitly DENY access to the KeepOutPlebs schema to "Domain Users" but then GRANT SELECT permissions to individual users on the table SuperSecretData? – J.D. Oct 2 at 13:31
  • Well no one can select from the view or table without permissions. You just need to ensure that no one has a global SELECT permission in the database. An alternate route would be to encrypt the data in the table with a symmetric key, and write a view that reads the data with DECRYPTBYKEYAUTOASYMKEY docs.microsoft.com/en-us/sql/t-sql/functions/… That way a database-level SELECT permission wouldn't grant access to the data. – David Browne - Microsoft Oct 2 at 13:47
  • Sorry yes, I should've specified more clearly, "Domain Users" is globally granted SELECT on most databases on our SQL server (when mapping the databases at the Server - Logins level). Given that being the case, wouldn't this view that decrypts the table also be accessible to "Domain Users"? – J.D. Oct 2 at 14:22
  • 1
    They could SELECT from the view, but not OPEN the SYMMETRIC KEY. That's a separate permission. – David Browne - Microsoft Oct 2 at 14:23
  • 1
    See update in answer. – David Browne - Microsoft Oct 2 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.