Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

The difference between REVOKE and DENY

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

Comments

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.

Leave a Comment

Please register or log in to leave a comment.