This was very interesting question recently asked me to during my session at TechMela Nepal. The question is what is the difference between GRANT and WITH GRANT when giving permissions to user.

Let us first see syntax for the same.

GRANT:
USE master;
GRANT VIEW ANY DATABASE TO username;
GO

WITH GRANT:
USE master;
GRANT VIEW ANY DATABASE TO username WITH GRANT OPTION;
GO


The difference between both of this option is very simple. In case of only GRANT – username can not grant the same permission to other users. In case, of the option of WITH GRANT – username will be able to give the permission it has received to other users.

This is very basic definition of the subject. I would like to request my readers to come up with working script to prove this scenario. If can submit your script to me by email (pinal ‘at’ sqlauthority.com) or in comment field.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SQL Permissions