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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Simple Installation of Master Data Services (MDS) and Sample Packages – Very Easy
Next Post
SQL SERVER – Enable Identity Insert – Import Expert Wizard

Related Posts

13 Comments. Leave new

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

    Reply
  • what is difference between grant and withgrant in sqlserver?
    what is difference between read and denyread in sqlserver?

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

      Reply
  • i want ask, how to protect tables contents,where everytime want to open table,must require password or how to encrypt data in tables?

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

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

    Reply
  • 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

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

    Reply
  • 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

    Reply
  • César Terrones
    June 7, 2019 9:46 am

    QUESTION 44
    You assign User1 a set of permissions that include the WITH GRANT OPTION.
    The WITH GRANT OPTION enables User1 to:
    A. request a log of permission use.
    B. delegate permissions to other users.
    C. create new database users.
    D. view other users’ permissions

    I answered B but the exam simulator states C, so I disagree.

    Reply
  • thank you

    Reply

Leave a Reply

Menu