Does anyone know how to programmatically decrypt the stored procedures (In
SQL Server 2000) encryted with the "With Encryption" clause?

Thanks a lot,
Lixin

What relevance does this have to DTS?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"FLX" <nospam (AT) hotmail (DOT) com> wrote

Hi FLX,

Thanks for using MSDN Newsgroup!

First of all, thanks the reply of MVP Allan Mitchell. If you only want to
know how to decrypt stored procedure, to keep the integrity of newsgroup,
it is highly appreciated if you could post your issue to the right place,
which, I believe, will be better for you to discuss it with community
members

Secondly, from your descriptions, I understood that you may want to decrypt
the stored procedures in DTS so that you could distribute the stroed
procedures with DTS package, Have I understood you? If there is anything I
misunderstood, please feel free to let me know

Based on my scope, You can use the built-in function DECRYPT or your own
decrypt function to create the encrypted stored procedure object when the
SQL script is executed.

1. In the Data Transformation Services (DTS) package:
a. Copy the text of the encrypted stored procedure creation
script(Use the Generate SQL Scripts dialog box) into the Execute SQL task.
b. Set the owner and user password for the package.
c. Distribute the package and give package users only the user
password so that they can execute the package.

NOTE: Users cannot open or edit the package because they do not have the
owner password. For additional information, visit the following Microsoft
Web site:
Save DTS Package

2. Create a database with encrypted objects and use this database for the
distribution of encrypted objects. This distributed database can be
attached or restored in the target server so that users can use the
encrypted objects in the database.

3. Write your own application to encrypt SQL Server script files and use
custom logic to decrypt the encrypted scripts files. Additionally, run the
decrypted scripts to create the encrypted objects by using the WITH
ENCRYPTION clause.


Hopt it helps and if you have any questions or concerns, please feel free
to let me know


Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!