Testing Grant and Deny permissions in SQL Server.
Here’s a rough breakdown of the steps we followed:
- Create a windows group, and create 2 users in the group (We’ll call them test1 and test2).
- Make a new database called SecurityTest.
- Add the windows group from earlier as a SQL LOGIN.
- Add a USER for the login in the SecurityTest database, with no permissions (yet).
- Create a table and put some data™ in it (We’ll call it SuperSecretTable™).
- Grant select permission to the USER (Group).
- Verify that users test1 and test2 can see the data™ (You’ll have to logon to the machine as these users).
- Test2 takes a screenshot of the SuperSecretTable™ and posts it on Twitter, gets 3 likes.
- I login as myself and specifically DENY test2 rights to view the table because he cannot be trusted with the data in the SuperSecretTable™ (Or crayons).
- Test2 logs in again, and finds himself unable to even see the SuperSecretTable™, much less the data™ in it, even though he’s in a group that has been GRANTed SELECT permissions.
In doing this, we’ve proved that DENY takes precedence over GRANT, because we’re cool like that
Eugene Meidinger says
Bonus question: what if you grant user to select permissions on individual columns, after the table level deny.
After reading your comment, I decided to do some testing, and I found the following results:
1. GRANTing select on one column of a table removes the DENY at the table level, with the end result of allowing test2 to see the entire table again, since the usergroup can select the entire table
2. Setting DENY at the table level for test2 removes the GRANT at the column level, effectively making test2 unable to see anything in the table.
3. We can still enable test2 to see one column in the table but not the rest by explicitly GRANTing and DENYing at the column level.
*In my enviroment, your mileage may vary