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. The key is the WITH GRANT OPTION clause.
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.
A Simple Way to Test GRANT and WITH GRANT OPTION
Since I asked readers for a script, here is the idea you can try on a test database. Create two test users with CREATE USER UserA WITHOUT LOGIN; and the same for UserB, plus a table such as dbo.Orders. Run the setup as a member of db_owner so you have the rights to do all of it.
- Run
GRANT SELECT ON dbo.Orders TO UserA WITH GRANT OPTION; - Switch to that user with
EXECUTE AS USER = 'UserA';and runGRANT SELECT ON dbo.Orders TO UserB;It works. Then runREVERT; - Repeat the test after a plain GRANT without the option. This time UserA gets an error when trying to pass the permission on.
Taking the permission back has a small twist. If you try REVOKE SELECT ON dbo.Orders FROM UserA; SQL Server complains, because UserA could have passed it on. Add CASCADE to the REVOKE, and the permission is removed from UserA and from anyone UserA granted it to.
You can see the difference in sys.database_permissions. A normal grant shows GRANT in the state_desc column, while the other one shows GRANT_WITH_GRANT_OPTION.
My advice: hand out the grant option rarely. Every person who has it can widen access without asking you, and six months later nobody remembers who gave what. Clean up the test users when you are done.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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