Copying encrypted stored procedures.

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.
HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de

Copying encrypted stored procedures.

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.(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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Copying encrypted stored procedures.

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.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
--
http://www.sqlerver2005.de
--

Copying encrypted stored procedures.

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.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
--

Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)

Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)