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

SQL Server Join Hints

There are mainly three type of join hints
1. Nested Loop Join
2. Merge Join
3. Hash Match Join

A join hint provides a means to force SQL Server to use one of the three join methods that we've encountered previously, in a given part of a query. To recap, these join methods are:
Nested Loops join: compares each row from one table ("outer table") to each row in another table ("inner table") and returns rows that satisfy the join predicate. Cost is proportional to the product of the rows in the two tables. Very efficient for smaller data sets.

Merge Join: compares two sorted inputs, one row at a time. Cost is proportional to the sum of the total number of rows. Requires an equi-join condition. Efficient for larger data sets.
Hash Match join: reads rows from one input, hashes the rows, based on the equi-join condition, into an in-memory hash table. Does the same for the second input and then returns matching rows. Most useful for very large data sets (especially data warehouses).

By incuding one of the join hints in your T-SQL, you will potentially override the optimizer's choice of the most efficent join method. In general, this is not a good idea, and if you're not careful you could seriously impede performance.1
Application of the join hint applies to any query (SELECT, INSERT, or DELETE) where joins can be applied. Join hints are specified between two tables.

SQL Server Execution Plans:

SQL Server Execution Plans:

If the following criteria are met, the plan is removed from memory:
1. more memory is required by the system
2. the "age" of the plan has reached zero
3. the plan isn't currently being referenced by an existing connection.

Execution plans are not sacrosanct. Certain events and actions can cause a plan to be recompiled. It is important to remember this, because recompiling execution plans can be a very expensive operation.
The following actions can lead to recompilation of an execution plan:
1. changing the structure or schema of a table referenced by the query
2. changing an index used by the query
3. dropping an index used by the query
4. updating the statistics used by the query
5. calling the function, sp_recompile
6. subjecting the keys in tables referenced by the query to a large number of Inserts or deletes (which leads to statistics changes)
7. for tables with triggers, significant growth of the inserted or deleted tables
8. mixing DDL and DML within a single query, often called a deferred compile
9. changing the SET options within the execution of the query
10. changing the structure or schema of temporary tables used by the query
11. changes to dynamic views used by the query
12. changes to cursor options within the query
13. changes to a remote rowset, like in a distributed partitioned view
14. when using client-side cursors, if the FOR BROWSE options are changed.