How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL

Question

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:

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.

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.

score 6 · Accepted Answer · edited Apr 13 '17 at 12:42

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 
    @server=N'DAC_LOOPBACK', 
    @optname=N'rpc', 
    @optvalue=N'true'
EXEC master.dbo.sp_serveroption 
    @server=N'DAC_LOOPBACK', 
    @optname=N'rpc out', 
    @optvalue=N'true'
GO

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
   'SomeEncryptedProcedure'

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.

How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL

1 Answers1

Linked