Invoke-DbaDbDecryptObject

Want to see the source code for this command? Check out Invoke-DbaDbDecryptObject on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDbDecryptObject.

Synopsis

Returns the decrypted version of an object

Description

SQL Server provides an option to encrypt the code used in various types of objects. If the original code is no longer available for an encrypted object it won't be possible to view the definition. With this command the dedicated admin connection (DAC) can be used to search for the object and decrypt it.

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

To connect to a remote SQL instance the remote dedicated administrator connection option will need to be configured. The binary versions of the objects can only be retrieved using a DAC connection. You can check the remote DAC connection with: 'Get-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled' It should say 1 in the ConfiguredValue.

The local DAC connection is enabled by default.

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.

Syntax

Invoke-DbaDbDecryptObject
    [-SqlInstance] <DbaInstanceParameter>
    [[-SqlCredential] <PSCredential>]
    [-Database] <Object[]>
    [[-ObjectName] <String[]>]
    [[-EncodingType] <String>]
    [[-ExportDestination] <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1

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

Example: 2

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

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

Example: 3

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ExportDestination C:\temp\decrypt

Decrypt all objects in DB1 of instance SQLDB1 and output the data to the folder "C:\temp\decrypt"

Example: 4

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1, Function2

Decrypt objects "Function1" and "Function2" and output the data to the user.

Example: 5

PS C:\> "SQLDB1" | Invoke-DbaDbDecryptObject -Database DB1 -ObjectName Function1, Function2

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

Required Parameters

-SqlInstance

The target SQL Server instance

-Database

Database to search for the object.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

-ObjectName

The name of the object to search for in the database.

-EncodingType

The encoding type used to decrypt and encrypt values.

-ExportDestination

Location to output the decrypted object definitions. The destination will use the instance name, database name and object type i.e.: C:\temp\decrypt\SQLDB1\DB1\StoredProcedure

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.