SQL SERVER – Difference Between GRANT and WITH GRANT

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

Let us first see the syntax and analyze.

USE master;

USE master;

The difference between these options is very simple. In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users.

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

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

12 thoughts on “SQL SERVER – Difference Between GRANT and WITH GRANT

  1. I really like u r post on all the topics. Once I have started using u r blogs, always keen to find out everything on u r blog, now its first search option for me.


    • Seriously? As explained above granting permission Y to user X with the option ‘with grant’ allows user X to grant permission Y to other users.


  2. I have had an issue with using the sp_send_dbmail which is wrapped in a shell procedure. My application login only has access to execute this shell procedure and is also a member of the DatabaseMailUserRole in msdb..

    But when I go to execute the email is generated but getting an error saying that the shell proc doesn’t exist or you dont have permission. SO if you grant with grant option, the error goes away..


  3. Hi Gents… I only have a question about error message 916: user xy want to expand database on object explorer. He get follow error message: The server principal “xxxx” is not able to access the database “yyyyy” under the current security context. (Microsoft SQL Server, Error:916)

    What I have done: reset view, refresh, Collation enabled / disabled, user permission changed from read/writhe to owner….

    Heeeeelp I always get mentioned error message.


  4. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  6. Could your please help me on this.
    I have to grant access for all stored procedures to user and deny access for alter tables for the same user.


  7. when i execute Proc: ( from userA have db_owner, the proc contains With execute as owner )

    use profile
    go execute prc_update_userobject_Ownerserver

    Error: principal “sa” is not able to access the database “AdvDB” under the current security context


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s