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)
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.
what is difference between grant and withgrant in sqlserver?
what is difference between read and denyread in sqlserver?
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.
i want ask, how to protect tables contents,where everytime want to open table,must require password or how to encrypt data in tables?
Just deny all permissions to that table
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..
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.
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
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.
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
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.
i will answer B like you.
thank you