如何從過程的程式碼中刪除 WITH ENCRYPTION - 通過 T-SQL

我有許多程序在創建它們時使用WITH ENCRYPTION選項。

您可以在下圖中看到這一點，例如：

問題是當我想保存我以前版本的儲存過程時，我無法獲得預期的結果，如下圖所示。

我備份儲存過程程式碼的方式在下面的連結上： 如何備份儲存過程的目前程式碼及其權限？

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 ...

有沒有辦法在我保存之前從儲存過程的程式碼中刪除 WITH ENCRYPTION？

前提是我擁有該程序的所有權。

下面的連結上有一個幾乎重複的問題 How to view an encrypted view or stored procedure

但是，在這個問題上，我擁有數據庫。我是系統管理員。我可以使用任何資源，但我想要一個通過 T-SQL 的解決方案。

正如 Sebastian Meine 對How to view an encrypted view or stored procedure問題的回答中所述，有一個 T-SQL 解決方案來執行解密。可以在Sebastian 的部落格上找到執行此操作的程式碼。

該解決方案需要與 DAC 連接，因此您只有很少的選項可以從 T-SQL 儲存過程中執行此操作。一種可能的方法是使用連結伺服器：

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

一旦您擁有使用 DAC 的環回連結伺服器，您就可以執行 Sebastian 的儲存過程來收集加密文本。不幸的是，該過程返回一個 XML 列（不能通過連結伺服器返回），因此您必須更改最後一個SELECT以返回 nvarchar(max)：

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

現在您可以使用連結伺服器呼叫該過程：

EXEC DAC_LOOPBACK.SomeDatabase.dbo.ObjectEncryptionCracker
  'SomeEncryptedProcedure'

我推薦這種方法嗎？一定不行！

跟踪儲存過程更改的正確方法是原始碼控制，正如您在原始問題中已經被告知的那樣。不要依賴此程式碼來備份您的程式碼（它不受支持並且使用了多種技巧）。幫自己一個忙，將您的數據庫對象置於原始碼控制之下。

引用自：https://dba.stackexchange.com/questions/115056