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.