By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Copying encrypted stored procedures.

P: n/a
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?

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.

Any advice would be greatly appreciated.

Nick.

Mar 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(ni*************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.