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
/*Please work*/,
The Apprentice
