TIL: Security Stuff™

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

Leave a Comment

Sign up for our Newsletter

%d bloggers like this: