Subscribe to this blog
Briefcase
Print
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
Posted by K. Brian Kelley on 1 July 2011
I like to explain REVOKE as the overall undo option. Because if you have a DENY you want to get rid of, you use REVOKE there, too.
Posted by rackerland on 30 January 2013
I know I'm late to the party here, but a DENY is also necessary if the granted permissions are implied. For example, if you grant the datareader role to a user, but then want to prevent that user from selecting from one specific table or column, you need to use DENY. Revoking SELECT on that table or column will have no effect since there is no explicit SELECT granted.