Category: Sql Server

How to Unlock Table in SQL Server?

To find out the tables on which locks are applied, use below step 1 and step 2 to kill that process id to release/unlock the table lock.

Step 1: Get the request session id by executing following SQL statement:

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
JOIN
    sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE
  OBJECT_NAME(P.object_id) = 'table_name'

Step 2: Kill the request session id which has kept lock on the table ‘table_name‘. Assume its request_session_id is 61. Execute following query:

 Kill 61

We can check or get closer look or find all blocking or locking on database tables by following script:

   SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,

     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,

     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

Hope this will help !!!

Minimizing impact of widening an IDENTITY column

A problem that I have seen crop up a few times recently is the scenario where you have created an IDENTITY column as an INT, and now are nearing the upper bound and need to make it larger (BIGINT). If your table is large enough that you’re hitting the upper bound of an integer (over 2 billion), this is not an operation you can pull off between lunch and your coffee break on a Tuesday. This series will explore the mechanics behind such a change, and different ways to make it happen with varying impacts on uptime. In the first part, I wanted to take a close look at the physical impact of changing an INT to a BIGINT without any of the other variables.

Read Article series by Aaron Bertrand who provided different solutions with analysis – http://sqlperformance.com/2016/01/sql-indexes/widening-identity-column-1

SQL Injection through SQLMap Burp Plugin

SQL Injection (SQLi) is a web based attack used by hackers to steal sensitive information from organizations through web applications. It is one of the most common application layer attacks used today. This attack takes advantage of improper coding of web applications, which allows hackers to exploit the vulnerability by injecting SQL commands into the prior web application.The underlying fact that allows for SQLi is that the fields available for user input in the web application allow SQL statements to pass through and interact with or query the database directly.

For example, let us consider a web application that implements a form-based login mechanism to store the user credentials and performs a simple SQL query to validate each login attempt. Here is a typical example:

select * from users where username=’admin’ and password=’admin123′;

If the attacker knows the username of the application administrator is admin, then he can log into the app as admin by entering the username as admin’– and without supplying any password. The query in the back-end looks like:

Select * from users where username=’admin’–’ and password=’xxx’;

Note the comment sequence (–-) causes the followed query to be ignored, so query executed is equivalent to:

Select * from users where username=’admin’;

Hence the password check is bypassed and the attacker is logged into the app as admin. SQL Injection can be tested in two ways – Manual Pen-Testing & Automation.

Read full articlehttp://www.securitylearn.net/tag/sqlmap-tutorial/

Hope this will help !!!!!

SQL Tips – Generate Script files for deployment quickly using SQLCMD command utility

Often we need DROP and CREATE scripts for Stored procedure modified during development of any CR to deployment them in ST/UAT/PROD environment.

I know I can simply right click the database and ‘Tasks > Generate Scripts’, but that doesn’t script the stored procedure in the template I want.

I need the stored procedure to be scripted in the same template you get when you right click the stored procedure from the object explorer and ‘Script Stored Procedure As > DROP and CREATE’.

To achieve this I found one solution which can help me to reduce my effort during deployment.

Here, we can use SQLCMD utility and achieve what we need. I am sharing the same with you so that you can also get benefit out of this.

Let us discuss the steps to achieve this.

Step 1 – Create the scripting stored procedure, this stored procedure will help us to generate the scripts in the template we want.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GenerateScriptTip]') 
AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_GenerateScriptTip]
GO

CREATE PROCEDURE [dbo].[usp_GenerateScriptTip] 
(
  @ObjectID INT,
  @Name NVARCHAR(128),
  @SchemaID INT
) 
AS 

DECLARE @code VARCHAR(MAX), @newLine CHAR(2)
SET @newLine = CHAR(13) + CHAR(10)

SET @code = 
    'USE [' + DB_NAME() + ']' + @newLine + 'GO' + @newLine + @newLine
    + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = '
    + 'OBJECT_ID(N''[' + SCHEMA_NAME(@schemaID) + '].[' + @Name + ']'') ' 
    + 'AND type IN (N''P'', N''PC''))' + @newLine 
    + 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaID) + '].[' + @name + ']' 
    + @newLine + @newLine + 'SET ANSI_NULLS ON' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER ON' + @newLine + 'GO'
    + @newLine + @newLine
    + OBJECT_DEFINITION(@ObjectID) + @newLine + 'GO' 
    + @newLine + @newLine + 'SET ANSI_NULLS OFF' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER OFF' + @newLine + 'GO'

WHILE @code <> ''
BEGIN
  PRINT LEFT(@code,8000)
  SET @code = SUBSTRING(@code, 8001, LEN(@code))
END
GO

Step 2 – Next, we need to enable Command shell which we are going to run to generate the SQL files for each stored procedures.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Step 3 – Run the below script to generate the templates we want. These SQL files we can use during deployment on ST/UAT/PROD environment.

DECLARE 
  @name sysname,
  @objID int,
  @schemaID int,
  @cmd varchar(1000),
  @folder varchar(128)

--This folder should exist on SQL DB server, files generated will be saved at SQL DB server C Drive. 
SET @folder = 'C:\Scripts'

--You can update the below query to select Stored procedures which are needed in deployment.
DECLARE procs CURSOR FOR 
	SELECT name, object_id, schema_id 
	FROM sys.procedures
	WHERE name in 
	(
	'Stored Procedure 1',
	'Stored Procedure 2',
	'Stored Procedure 3'
	)
	ORDER BY [name]

OPEN procs

FETCH NEXT FROM procs
	INTO @name, @objID, @schemaID

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME() 
           + ' -Q "EXEC usp_GenerateScriptTip ' 
           + CONVERT(VARCHAR(20), @objID) + ', N'''
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID) 
           + '" &gt; ' + @folder + '\' + @name + '.sql'

  EXEC xp_cmdshell @cmd

  FETCH NEXT FROM procs
  INTO @name, @objID, @schemaID

END
CLOSE procs
DEALLOCATE procs

You can also update select query of above Cursor with below query which fetches the stored procedure names which are updated recently for CR-1234. Here CR-1234 must be present in the stored procedures which you have modified as a comment, which we usually do during development.

SELECT name, object_id, schema_id 
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%CR-1234%'

Similarly, You can generate DROP and CREATE scripts for New Tables. Do not use this if table is already exists and you want to alter the definition of table.

DECLARE tabls CURSOR FOR  
SELECT name, object_id, schema_id  
FROM sys.Tables
WHERE is_ms_shipped = 0  
ORDER BY [name] 
OPEN tabls 

FETCH NEXT FROM tabls 
INTO @name, @objID, @schemaID 

WHILE @@FETCH_STATUS = 0 
BEGIN 

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME()  
           + ' -Q "EXEC usp_GenerateScriptTip '  
           + CONVERT(VARCHAR(20), @objID) + ', N''' 
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID)  
           + '" &gt; ' + @folder_Tbl + '\' + @name + '.sql' 

  EXEC xp_cmdshell @cmd 

  FETCH NEXT FROM tabls 
  INTO @name, @objID, @schemaID 

END 

CLOSE tabls 
DEALLOCATE tabls 

That is all.

Hope this will help and speed up your deployment process.

For more details on SQLCMD command – click here.

Jay Ganesh !!!!!

SQL Server Data Tools to Visual Studio 2013 – Database Reverse Engineering

Microsoft created SQL Server Data Tools for Visual Studio 2013, to make development easier.

  • Single Tool to support developer’s needs
  • Can build, debug, test, maintain, and refactor databases
  • Developers can use familiar Visual Studio tools for database development
    • Code navigation, IntelliSense, C# language, platform-specific validation
      • Debugging, and declarative editing in the Transact-SQL editor
  • Works connected or disconnected (Tools work on top of design time)
  • Have Schema Model differencing capabilities (Compare and Update Model)
  • Schema and app under TFS control
  • Publish to all supported SQL platforms

Read Full Article – Click

Hope this will help !!!

Jay Ganesh !!!!!!