SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Simple Way to Decrypt SQL Server Stored Procedure

When stored procedures are created in SQL Server, their entire text body is accessible to all those who have the required permissions for accessing the data. Therefore, it becomes very easy to expose the underlying object content by creating stored procedures and analyzing it content via SQL Server Management Studio, Query Analyzer, Windows PowerShell, or any commercial utility. This data transparency, as a result, poses a risk of data compromises by the potent cyber criminals. Therefore, SQL Server developers consider encryption, the most suitable way to authenticate their data.

Need For Decrypting SQL Server Stored Procedure

Even though, encryption of stored procedures of SQL Server ensures that the objects cannot be accessed and read easily, at times it poses some issues to the users. Being a SQL Admin, I have come across many issues where the users no longer had access to their decryption script and therefore were not able to decrypt the database when required.

In certain scenarios, it happens that the administrators are handed over encrypted, SQL databases to work on. In order to work with them, the first thing that the admin needs is the encryption script and in the absence of it, the admin go for decrypting the database.

Procedure to Decrypt Stored Procedure in SQL Server

The first thing that needs to be done is to open a DAC (Dedicated Administrator Connection) to the SQL Server. It is to be noted that the DAC can only be used in three conditions:

  • The user is logged in the server.
  • The user is using a client on that server.
  • The user holds the sysadmin role.

Keep in mind that the DAC will not work if the user is not using TCP/IP and will show cryptic error if TCP/IP is not used.

The process is mainly divided into three sections:

  1. The first step is to get the encrypted value from sys.sysobjvalues via DAC connection.
  2. The next step is to take out the encrypted value of some blank procedure.
  3. Get the unencrypted blank procedure statement in plaintext format. Apply XOR to all the results. XOR is the simplest decryption procedure and is the basic algorithm used in MD5.
PRINT ‘This is decrypted database’
SET @encrypted = (
SELECT imageval
FROM sys.sysobjvalues
WHERE OBJECT_NAME(objid) = ‘TestDecryption’)
DECLARE @encrypedLength INT
SET @encryptedLength –DATALENGTH(@encrypted) / 2
DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N ‘ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS’
SET @procedureHeader = @procedureHeader + REPLICATE(N ‘-‘,
(@encryptedLength –LEN(@pocedureHeader)))
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptMessage = ‘’
SET @cnt = 1
WHILE @cnt <> @encryptedLength
SET @decryptedChar =
@encrypted, @cnt, 1)) ^
@procedureHeader, @cnt, 1)) ^
@blankEncrypted, @cnt, 1)) ^
SET @decryptedMessage = @decryptedMessage + @decryptedChar
SET @cnt = @cnt + 1
SELECT @decryptedMessage


SQL Server database encryption ensures database authenticity from unwanted users. However, this may at times pose a problem for the user. With the help of the above-mentioned script, the user can easily decrypt their stored procedures in SQL Server. However, if the above procedure doesn’t work for you, then going with a third party SQL decryptor tool is the best solution.

Ganapathi's MSSQLLover

Ganapathi Varma is an MCP Lead Database Administrator and Blogger. He works as Lead SQL Server DBA for CtrlS Datacenters pvt ltd, Asia's only Tier 4 datacenter. He is responsible for managing thousands of SQL instances providing database managed services, High Availability and Disaster Recovery. Other than SQL Server, he is also expertised in different datacenter technologies. He is the owner of MSSQLLover blog and he is fully dedicated to the SQL Server community. You can also visit the mssqllover fb page for more SQL blogs and follow FB group: SQLHyderabad.


Leave a comment on the original post [mssqllover.blogspot.com, opens in a new window]

Loading comments...