How to remove encryption from all objects in SQL Server?

Below is a PowerShell example that creates a script file of all encrypted objects, gleaned from Paul White's The Internals of WITH ENCRYPTION article. Change the data source and initial catalog in the 2 connection strings to the desired server and database as well as script file path.

A DAC connection is used to retrieve values from system tables so sysadmin server role membership is required. If run remotely, the SQL Server remote admin connections option must be enabled and TCP port 1434 allowed through the firewall.

The script can be run from the PowerShell ISE or from a command prompt after customization. Example command-line invocation, assuming script was saved to file "Decrypt-Objects.ps1".

powershell -ExecutionPolicy RemoteSigned -File C:\PowershellScripts\Decrypt-Objects.ps1

PowerShell script:

# PowerShell implementation of T-SQL code from https://sqlperformance.com/2016/05/sql-performance/the-internals-of-with-encryption

Function Get-DecryptedString($pwd, $data) {

    $key = [System.Array]::CreateInstance([int], 256)
    $box = [System.Array]::CreateInstance([int], 256)
    $cipher = [System.Array]::CreateInstance([byte], $data.Length)

    for ($i = 0; $i -lt 256; ++$i) {
      $key[$i] = $pwd[$i % $pwd.Length]
      $box[$i] = $i
    }

    for ($j = $i = 0; $i -lt 256; ++$i) {
      $j = ($j + $box[$i] + $key[$i]) % 256
      $tmp = $box[$i]
      $box[$i] = $box[$j]
      $box[$j] = $tmp
    }

    for ($a = $j = $i = 0; $i -lt $data.Length; ++$i) {
      ++$a
      $a %= 256
      $j += $box[$a]
      $j %= 256
      $tmp = $box[$a]
      $box[$a] = $box[$j]
      $box[$j] = $tmp
      $k = $box[(($box[$a] + $box[$j]) % 256)]
      $cipher[$i] = ($data[$i] -bxor $k)
    }

    $decryptedString = [System.Text.Encoding]::Unicode.GetString($cipher)

    return $decryptedString

}


Function Get-ClearObjectText($connectionString, $objectName) {

$getRc4KeyQuery = @"
DECLARE
    @objectid integer = OBJECT_ID(@ObjectName),
    @family_guid binary(16),
    @objid binary(4),
    @subobjid binary(2);
 
-- Find the database family GUID
SELECT @family_guid = CONVERT(binary(16), DRS.family_guid)
FROM sys.database_recovery_status AS DRS
WHERE DRS.database_id = DB_ID();
 
-- Convert object ID to little-endian binary(4)
SET @objid = CONVERT(binary(4), REVERSE(CONVERT(binary(4), @objectid)));
 
SELECT
    -- Read the encrypted value
    @imageval = SOV.imageval,
    -- Get the subobjid and convert to little-endian binary
    @subobjid = CONVERT(binary(2), REVERSE(CONVERT(binary(2), SOV.subobjid)))
FROM sys.sysobjvalues AS SOV
WHERE 
    SOV.[objid] = @objectid
    AND SOV.valclass = 1;
 
-- Compute the RC4 initialization key
SELECT @RC4key = HASHBYTES('SHA1', @family_guid + @objid + @subobjid);
"@

    $connection = New-Object System.Data.SqlClient.SqlConnection($dacConnectionString)
    $connection.Open()
    
    $command = New-Object System.Data.SqlClient.SqlCommand($getRc4KeyQuery, $connection)
    ($command.Parameters.Add("@ObjectName", [System.Data.SqlDbType]::NVarChar, 261)).Value = $objectName
    ($command.Parameters.Add("@imageval", [System.Data.SqlDbType]::VarBinary, -1)).Direction = [System.Data.ParameterDirection]::Output
    ($command.Parameters.Add("@RC4key", [System.Data.SqlDbType]::Binary, 20)).Direction = [System.Data.ParameterDirection]::Output
    [void]$command.ExecuteNonQuery()
    $imageval = $command.Parameters["@imageval"].Value
    $RC4key = $command.Parameters["@RC4key"].Value
    $connection.Close()

    $decryptedString = Get-DecryptedString -pwd $RC4key -data $imageval

    Return $decryptedString


}

# ############
# ### MAIN ###
# ############

# DAC connection string for decryption
$dacConnectionString = "Data Source=admin:YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"

# normal connection string for encrypted object list
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"

# target file path for clear encrypted objects DDL
$scriptFilePath = "C:\Scripts\EncryptedObjects.sql"
[void](New-Item -Path "C:\Scripts\EncryptedObjects.sql" -ItemType file -Force) # create directory (if needed) and empty script file

$EncryptedObjectQuery = @"
SELECT
     QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name) AS QualifiedObjectName
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1;
"@

try {

    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($EncryptedObjectQuery, $connection)
    $connection.Open()

    $reader = $command.ExecuteReader()

    while ($reader.Read()) {

        $createObjectScript = Get-ClearObjectText -connectionString $dacConnectionString -objectName $reader["QualifiedObjectName"]

        $createObjectScript | Out-File -FilePath $scriptFilePath -Append
        "GO" | Out-File -FilePath $scriptFilePath -Append

    }

    $connection.Close()

}
catch {

    throw

}