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.

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 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)

About these ads

11 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. your blog is really very much helpful.
    i would like to know diff between grant and joins too.
    can u give clarification on that topic

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

  7. 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.

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