I have many procedures that I use the option WITH ENCRYPTION when I create them.

You can see this on the picture below, as an example:

enter image description here

the problem with this is that when I want to save my previous version of my stored procedure, I cannot get the expected result, as you can see on the picture below.

enter image description here

the way I am backing up the code of the stored procedures is on the link below: How to backup the current code of a stored procedure and its permissions as well?

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc 
    , *
From sys.procedures as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where ...

is there a way to remove the WITH ENCRYPTION from the code of a stored procedure, before I save it?

Provided I have ownership of the procedure.

There is a nearly duplicated question on the link below How to view an encrypted view or stored procedure

however, on this question, I own the database. I am sysdba. I can use any resources, but I would like a solution via T-SQL.

  • 2
    You're doing it the wrong way round. The code should be in SSDT then push changes to the database server - not change definitions in the database and copy them out after. – Martin Smith Sep 15 '15 at 12:18
  • @MartinSmith+1 because I agree with you. still this is something I would like to know. – Marcello Miorelli Sep 15 '15 at 13:04

As noted in Sebastian Meine's answer to the question How to view an encrypted view or stored procedure, there is a T-SQL solution to perform the decryption. The code to do so can be found on Sebastian's blog.

The solution requires connecting with the DAC, so you have very few options to do so from a T-SQL stored procedure. One possible way is using a linked server:

DECLARE @srv nvarchar(4000);
SET @srv = 'ADMIN:' + @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
    @server     = N'DAC_LOOPBACK',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
    @datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'DAC_LOOPBACK',
    @useself     = N'True',
    @locallogin  = NULL,
    @rmtuser     = NULL,
    @rmtpassword = NULL;

EXEC master.dbo.sp_serveroption 
EXEC master.dbo.sp_serveroption 
    @optname=N'rpc out', 

Once you have the loopback linked server using the DAC, you can execute Sebastian's stored procedure to gather the encrypted text. Unfortunately, the procedure returns an XML column (which cannot be returned over a linked server), so you will have to change the last SELECT to return nvarchar(max):

SET @cmd = N'SELECT CAST(@plain AS NVARCHAR(MAX)) AS [object_definition for ' + 
    REPLACE(@object_name, ']', ']]') + ']';

Now you can call the procedure using the linked server:

EXEC DAC_LOOPBACK.SomeDatabase.dbo.ObjectEncryptionCracker

Do I recommend this method? Hell, NO!
The proper way to keep track of changes in your stored procedures is source control, as you already have been told in your original question. Don't rely on this code for backing up your code (it's unsupported and uses several hacks). Do yourself a favor and put your database objects under source control.

Not the answer you're looking for? Browse other questions tagged or ask your own question.