SQL Server – Scripting Out Encrypted Objects


For one of my database projects I needed to script out 3 databases including all objects, SSIS packages and SSRS reports.

There is no one-click solution, especially as many of the database objects are encrypted.

After scratching my head a fair bit, I came up with the following as the most efficient.

For the database objects I used the built-in “Generate Scripts” feature from within SSMS to script out the databases and all objects. By setting the option to continue after errors, I was left with a script with would create each database and all unencrypted objects.
The tricky bit was around accessing those encrypted objects. thankfully Red Gate tools came to the rescue.
I ran the following script to determine all the encrypted objects:

SELECT
 SS.Name + '.' + SO.Name AS ObjectName,
 SO.Type AS ObjectType
 FROM sys.SQL_Modules AS SM
 INNER JOIN sys.Objects AS SO ON SM.Object_Id = SO.Object_Id
 INNER JOIN sys.Schemas AS SS ON SO.Schema_Id = SS.Schema_Id
 WHERE SO.Type IN
 (
 'P', -- Procedures
 'V', -- Views
 'FN', -- Functions
 'T' -- Triggers
 )
 ORDER BY 2, 1

With this list, I used SQL Prompt to access the creation script for each object. It was a bit onerous to say the least as in total there were about 600 encrypted objects across the 3 databases, but it got the job done.

For the reports I used a great utility called RS Scripter, details at the following URL:
http://www.sqldbatips.com/showarticle.asp?ID=62

And for the SSIS packages I used a modified version of the following cheeky wee SSIS package, as I needed to pull them from a 2005 db:
http://www.ssistalk.com/2011/03/14/ssis-export-all-ssis-packages-from-msdb/

It took a while but I got there in the end !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s