Posted by Erland Sommarskog on 03/25/06 00:21

(nickwilson.nick@gmail.com) writes:
> I have several stored procedures, created in a development environment,
> that I need to move to a 'QA' environment, and then in turn, to various
> production environments.
> When I move these stored procedures, I would like to encrypt them,
> using the 'WITH ENCRYPTION' clause.
>
> My question is, how do I copy these stored procedures from development
> to their target SQL server environment in an encrypted state?

The answer is that you don't. I think SQL 7 had a function for this, but
it was discontinued with SQL 2000.

> Up until now, we have been moving them by generating an SQL script and
> then executing that script on the target server. I have tried this
> using a script with 'WITH ENCRYPTION' specified within it, but it
> doesn't appear to work when I try and execute that script on the target
> server.

What "doesn't appear to work" mean?

You would have to write a tool that reads the script and shifts in
WITH ENCRYPTION before AS. (Assuming that the number of procedures are
too many to make manual editing an option.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]