Today we’re going to cover a scenario you might face at work, one that’s also a topic you’d need to know in order to pass the 70-432 Database Administrator’s exam. If you have any questions in your preparation for the 70-432, let me know. I’ll be happy to help in any way!
Now for the scenario.
You have a table in your database it contains sensitive data. You want to block a certain user from being able to SELECT data from that table, but you don’t want to change any other permissions that user has in the database right now.
You might want to change those later…but for now, you just want to prevent him from selecting data.
What T-SQL command do you use?
You want to run an ADD USER command? Why do that? The user already exists. We want to change his SELECT permission.
TRUNCATE the table? That sure as hell would prevent him from reading the data in that table. But like I said, that data is sensitive. I don’t think your boss would like you Truncating the primary sales table for your company, just to prevent Joe Schmoe from reading sales figures.
DENY SELECT ON table?
BINGO! That’s the right answer! If you want to DENY a specific permission on a database object without changing any other permission, simply
DENY <permission> ON <OBJECT>
And the permission in question is removed!
It really is as easy as that.