There was confusion recently with a poster that was moving permissions around and asked why I said they should revoke permissions and not deny them. I decided this was worth a post to explain.

If I GRANT SELECT (or UPDATE/INSERT/DELETE) permissions to a user, then they can use those permissions to view data in a table. If I REVOKE the permissions, it’s the same as if the user never had them. They would need to be GRANTed permissions again to see the data.

However, if I DENY them the ability to see data, then that’s different. They can’t see the data, but a subsequent GRANT will not allow them to see the data because the DENY will still be in effect.

It’s a more permanent change, and should be used when you need to be sure that someone cannot see data, not when you are looking to remove permissions. To undo a GRANT, use REVOKE.

Filed under: Blog Tagged: security, sql server, syndicated, T-SQL