Difference Between Grant and With Grant Permission in SQL Server

Difference Between Grant and With Grant Permission in SQL Server



GRANT ALLOW THE CURRENT USER/GRANTED USER TO ACCESS THE GRANTED OBJECT AS PER THE GRANTED COMMAND(SELECT,DELETE,INSERT ETC..)

WITH GRANT OPTION IS NOTHING BUT A SUPER LEVEL OF ACCESS/GRANT PROVIDER. THIS WILL INFORM THE DATABASE
THAT, THIS USER WILL HAVE ACCESS TO THE PARTICULAR OBJECT AND HE/SHE IS HAVING NECESSARY PREVILEGE TO PROVIDE ACCESS TO OTHER USERS ON THE OBJECTS.


SUMMARY:
IN NUT SHELL WE CAN SAY GRANT ALLOW THE GRANTED USER TO ACCESS THE OBJECTS BUT WITH GRANT ALLOWED GRANTED USER TO ACCESS THE OBJECTS AND GIVE THE PERMISSION TO OTHER USERS TO ACCESS THE OBJECTS

Example:

STEP 1.
LET US I HAVE CREATED THREE LOGINS WITH PUBLIC ROLE WITH USER MAPPING TO DATABASE DB2
1. [GRANT]
2. [WITHGRANT]
3. [WITHOUTGRANT]

STEP 2.
ALSO I HAVE CREATED ONE EMP TABLE AND INSERTED ONE RECORD AS PER BELOW COMMANDS:

CREATE TABLE Emp (EmpId INT ,EmpName VARCHAR(100))

INSERT INTO Emp(EmpId,EmpName)
VALUES(1,'TestGrant')

STEP 3.
PROVING ACCESS NEWLY CREATED USERS:
NOW I HAVE PROVIDED GRANT ACCESS TO USER [GRANT] WITH SYSADMIN RIGHTS AS PER BELOW COMMAND
GRANT SELECT ON EMP TO [GRANT]

ALSO I HAVE PROVIDED WITH GRANT ACCESS TO USER [WITHGRANT] WITH SYSADMIN RIGHTS AS PER BELOW COMMAND
GRANT SELECT ON EMP TO WITHGRANT WITH GRANT OPTION

STEP 4.
NOW I HAVE CONNECTED DATABASE SERVER WITH LOGIN [GRANT] AND TRYING TO PROVIDE GRANT ACCESS ON TABLE EMP TO THIRD USER [WITHOUTGRANT] USING BELOW COMMAND
GRANT SELECT ON EMP TO [WITHOUTGRANT]

STEP 5.
AFTER EXECUTING THE ABOVE COMMAND I AM GETTING BELOW ERROR AS USER [GRANT] IS NOT SUPPOSE TO PROVIDE ACCESS ANY OTHER USER.
MESSAGE:
Msg 15151, Level 16, State 1, Line 3
Cannot find the object 'Emp', because it does not exist or you do not have permission.

STEP 6.
NOW AGAIN I HAVE CONNECTED DATABASE SERVER WITH LOGIN [WITHGRANT] AND TRYING TO PROVIDE GRANT ACCESS ON TABLE EMP TO THIRD USER [WITHOUTGRANT] USING BELOW COMMAND
GRANT SELECT ON EMP TO [WITHOUTGRANT]

AFTER EXECUTING THE ABOVE COMMAND I AM GETTING BELOW MESSAGE AS USER [WITHGRANT] IS SUPPOSE TO PROVIDE ACCESS ANY OTHER USER.
Message:
Command(s) completed successfully.

STEP 7.
NOW I HAVE CONNECTED TO DATABASE SERVER AND WITH LOGIN [WITHOUTGRANT] AND EXECTTED BELOW COMMAND ON DATABASE DB2.
SELECT * FROM Emp

GETTING RESULT WHICH I HAVE INSERTED IN STEP 2.:
EmpId EmpName
1 TestGrant