70-432: Revoking Permissions

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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *