dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Invoke-DbaDbDecryptObject

When a procedure or a function is created with encryption and you lost the code you’re in trouble. You cannot alter the object or view the definition. With this command you can search for the object and decrypt the it.

The command will output the results to the console. There is an option to export all the results to a folder creating .sql files.

Make sure the instance allowed dedicated administrator connections (DAC). The binary versions of the objects can only be retrieved using a DAC connection.
You can check the DAC connection with:
'Get-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled'
It should say 1 in the ConfiguredValue

To change the configurations you can use the Set-DbaSpConfigure command:
'Set-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled -Value 1'
In some cases you may need to reboot the instance.

Screenshots

dbatools-Invoke-DbaDbDecryptObject

Examples

Decrypt object “Function1” in DB1 of instance SQLDB1 and output the data to the user.

Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1

Decrypt object “Function1” in DB1 of instance SQLDB1 and output the data to the folder “C:\temp\decrypt”.

Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1 -Force -ExportDestination C:\temp\decrypt

Decrypt objects “Function1” and “Function2” and output the data to the user.

Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1, Function2

Decrypt objects “Function1” and “Function2” and output the data to the user using a pipeline for the instance.

“SQLDB1” | Invoke-DbaDbDecryptObject -Database DB1 -ObjectName Function1, Function2

Author

This command was created by Sander Stad. You can find Sander on Twitter and his blog.

Get-Help

From PowerShell, execute Get-Help Invoke-DbaDbDecryptObject -Detailed for more information on this function.

Source Code

Want to see the source code? View Invoke-DbaDbDecryptObject.ps1 on GitHub
 

Related commands