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.

Monitoring Tools and Resources Available in SQL Server

Know the Monitoring Tools and Resources Available in SQL Server
The primary monitoring tools you will use for SQL Server are the Reliability and Performance Monitor and the SQL Server Profiler. But there are other resources also available for monitoring SQL Server. These include:
Query Analyzer: This Query analyzer provides different info according to the template selected based on the usability and requirement i.e TSQL_Duration, TSQL_Count, TSQL_Locks etc.
Activity Monitor: This monitor provides information on current users, processes, and locks, as discussed in “Managing Server Activity”. To display Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Database Engine instance and then select Activity Monitor.
Replication Monitor: This monitor provides details on the status of SQL Server replication and allows you to configure replication alerts. To display Replication Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Replication node and then select Launch Replication Monitor.
SQL Server logs: The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems. To access the server logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the Management node. Under the Management node, expand the SQL Server Logs node and then double-click the log you want to examine.
Job Activity Monitor: This monitor provides details on the status of SQL Server Agent jobs. To display Job Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node, and then double-click Job Activity Monitor.
SQL Server Agent logs: The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems. To access agent logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node. Under the SQL Server Agent node, expand the Error Logs node and then double-click the log you want to examine.
Note that SQL Server documentation refers to the SQL Server and SQL Server Agent logs as error logs. In their current implementation, however, the logs are more accurately called event logs, which is the terminology used in this chapter. Similar to event logs in Microsoft Windows, these logs in SQL Server contain informational and security messages as well as error messages.
Event logs: The information in the event logs allows you to troubleshoot systemwide problems, including SQL Server and SQL Server Agent problems. To access event logs, click Start, click Administrative Tools, and then select Event Viewer.
DBCC statements: This set of commands allows you to check SQL Server statistics, to trace activity, and to check database integrity.
sp_helpdb: This stored procedure displays information about databases.
sp_helpindex: This stored procedure reports information about indexes on a table or view.
sp_helpserver: This stored procedure provides information in SQL Server instances configured for remote access or replication.
sp_monitor: This stored procedure shows key SQL Server usage statistics, such as CPU idle time and CPU usage.
sp_spaceused: This stored procedure shows an estimate of disk space used by a table, indexed view, or Service Broker queue in the current database.
sp_who: This stored procedure shows a snapshot of current SQL Server users and processes.
sp_who2: This stored procedure shows a snapshot of current SQL Server users and processes with more detailed information.
sys.dm_tran_locks: This dynamic management view shows information about object locks.
Note that the sys.dm_tran_locks view replaces the sp_lock stored procedure.
DMV/DMF: Dynamic management views/functions provided the database and OS level information’s
Example: select * from sys.dm_os_performance_counters  this dmv provides operating system level counters like Buffer cache hit ratio, Page reads/sec, Page life expectancy etc
select * from sys.dm_db_missing_index_details  this dmv provide the missing index details at database level
Built in function/Global variables(tSql): built in functions show the database information based on the usability
@@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code
@@CONNECTIONS contains the number of SQL Server connections or attempted connections
@@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections
Trace Flags (tSQL): Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).
Database Engine Tuning Advisor: Database Engine Tuning Advisor analyzes the performance effects of Transact-SQL statements executed against databases you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning.

DBCC (Database Consistency Check)

DBCC is very important command to check for specific Server/Database physical or logical information.

we have many DBCC commands, we use them as per the requirement and information we need.

some of them are:
1. DBCC INPUTBUFFER(SPID) ---> Where SPID is an integer values which indicate to specific sql server command reunning behind it.
Syntex:
DBCC INPUTBUFFER(77)

Result in Column EventInfo:
select TOP 10 * from sys.objects

The alternative for DBCC INPUTBUFFER is fn_get_sql(sql_handle) which require and sql_handle of that specific spid
Syntex:
First we need to find out sql handle of that spid(77) using below command then we need to pass that sql_handle value to fn_get_sql funciton.
select sql_handle from sys.sysprocesses where spid = 77
select * from ::fn_get_sql(0x01001E0060C9581A40622EB90000000000000000)
Reslut in Column text
select TOP 10 * from sys.objects


SOME OTHER COMMANDS ARE:


2. DBCC CHECKDB ----> Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

i. DBCC CHECKALLOCK
ii. DBCC CHECKTABLE
iii. DBCC CHECKCATALOG
iv: Validates the contents of every indexed view in the database.
v: Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in
the file system using FILESTREAM.
vi: Validates the Service Broker data in the database.

CHECKDB with some Other options (a) DBCC CHECKDB (AdventureWorks2012, NOINDEX);(b) DBCC CHECKDB WITH NO_INFOMSGS; DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS

3. DBCC useroptions ---> Returns the SET options active (set) for the current connection.
4. DBCC SHOW_STATISTICS('tbl_Employee', 'IX_tbl_Employee_EMPID') ---> to check the statistics info of index on a table
5. DBCC SHOWCONTIG ---> to check fragmentation information
6. DBCC OUTPUTBUFFER (71) ---> Input paramter is @@SPID , this command Returns the current output buffer in hexadecimal and ASCII
format for the specified session_id
7. DBCC PROCCACHE ---> Displays information in a table format about the procedure cache.
8. DBCC SQLPERF(LOGSPACE) ---> Displays Log File information of all available database on a given server
Output of this command look like:
Database Name Log Size(MB) Log Space Used (%) Status
TestDB 7.429688 16.41693 0
9. DBCC TRACESTATUS(-1); ---> Displays the status of trace flags.
10. DBCC OPENTRAN: ---> Displays information about the oldest active transaction and the oldest distributed and
nondistributed replicated transactions

There are other DBCC commands also we use these commands as and when required depends on the Usability and requirement.

NOLOCK vs READPAST IN SQL SERVER

NOLOCK and READPAST table hints in SQL Server


When data in a database is read or modified, the database engine uses special types of controls, called locks, to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.
When designing database applications, you should keep in mind the different types of locks that will be issued, and the different levels of isolation your transactions will occur. Typically, the SQL Server defaults work fine for what you are trying to accomplish. However, there will be times when it is advantageous to manually make hints to how locks are issued on your tables in your SQL statements.


NOLOCK
This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.
The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.
As a side note, NOLOCK queries also run the risk of reading "phantom" data, or data rows that are available in one database transaction read but can be rolled back in another.
The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the ItemMaster table.
BEGIN TRANSACTION
INSERT INTO ItemMaster
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', GETDATE(), 500)
The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script, which uses the NOLOCK table hint in returning the number of records in the ItemMaster table.
SELECT COUNT(1) FROM ItemMaster WITH(NOLOCK)
The number of records returned is 11. Since the transaction that entered the record into the ItemMaster table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
This statement removes the record from the ItemMaster table that I previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(1) FROM ItemMaster WITH(NOLOCK)
This time the record count returned is 10. My first query read a record that was not yet committed -- this is a dirty read.so out of 11 records 1 Item PoolTable was a dirty record.


READPAST
This is a much less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
The advantage of this table hint is that, like NOLOCK, blocking does not occur when issuing queries. In addition, dirty reads are not present in READPAST because the hint will not return locked records. The downside of the statement is that, because records are not returned that are locked, it is very difficult to determine if your result set, or modification statement, includes all of the necessary rows. You may need to include some logic in your application to ensure that all of the necessary rows are eventually included.
The READPAST table hint example is very similar to the NOLOCK table hint example. I'll begin a transaction and update one record in the ItemMaster table.

BEGIN TRANSACTION
UPDATE TOP(1) ItemMaster
SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the ItemMaster table to count the number of records in the table.
SELECT COUNT(1)
FROM ItemMaster WITH(READPAST)

My ItemMaster table originally had 10 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 9 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.
In ReadPast Row Level Locking are skipped
Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.
To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query
READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true.

• The transaction isolation level of the session is READ COMMITTED.
• The READCOMMITTED table hint is also specified in the query

Difference Between Exec and sp_executesql

/* Difference Between Exec and sp_executesql*/ sp_executesql (also known as "Forced Statement Caching") • Performance wise good, Recommended • Allows for statements to be parameterized • Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. • Has strongly typed variables/parameters – and this can reduce injection and offer some performance benefits! • Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan • Need NVARCHAR Data type otherwise will show error Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. EXEC (also known as "Dynamic String Execution" or DSE) • Performance wise not good, Not recommended • Do not Allows for statements to be parameterized • Allows *any* construct to be built • Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do – they are parsed, probably parameterized and possibly deemed "safe" for subsequent executions to re-use • Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed • Does not force a plan to be cached. o This can be a pro in that SQL Server can create a plan for each execution o This can be a con in the SQL Server needs to recompile/optimize for each execution o Works with VARCHAR and NVARCHAR -- Use the following query to test, CREATE TABLE [dbo].[TestExectuionPlan] ( ID INT ) GO INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (1) INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (2) GO --- Clean the existing plans DBCC FREEPROCCACHE DECLARE @ItemID INT DECLARE @Query NVARCHAR(200) SET @Query = 'SELECT ''Query Using EXEC'' AS ''EXEC'', * FROM [dbo].[TestExectuionPlan] WHERE ID = ' SET @ItemID = 1 EXEC( @Query + @ItemID) SET @ItemID = 2 EXEC( @Query + @ItemID) SET @Query = 'SELECT ''Query Using sp_executesql'' AS ''SP_EXECUTESQL'', * FROM [dbo].[TestExectuionPlan] WHERE ID = @ID' SET @ItemID = 1 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID SET @ItemID = 2 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- To view the execution plan, use the following query. SELECT usecounts, sql FROM sys.syscacheobjects where dbid = DB_ID()--- ( to retrieve the execution plan of current database) ---- Check the list of queries in output create for sp_executesql and exec