How to script all my stored procedures ENCRYPTED

Question

How to script all my stored procedures ENCRYPTED

rrodriguez

SSC Journeyman

Points: 84
More actions
November 15, 2002 at 7:59 am

#75979

I need to know if there is a way to script all the stored procedures with ENCRYPTION..automatically..

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

I don't think you can script the stored procedures which are encripted.They should be scripted and saved at the time they are created.

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 2

dOMNAR's SQL Server SysComments Decryptor:

http://www.geocities.com/d0mn4r/dSQLSRVD.html

Requires you to have sysadmin rights on a SQL Server 2000 installation.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 3

decrypter in the script library here as well.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 4

Just a heads up on the script in the repository. The script on the site uses the mechanism proposed by shoeboy. It gets around the need to know certain information that can only be queried by a sysadmin (or process running at that level) by using ALTER with a known plaintext. From that it can find out the original plaintext. It's not a complicated encryption mechanism.

The limitation is that it can only process stored procedures up to 4000 characters. If a stored procedure goes beyond 4000 characters, it'll actually contain multiple rows in syscomments. The script only handles the first row. Also, since it is having to execute the ALTER PROC, without generating some way of having multiple variables of 4000 Unicode characters tied together and executing them together. Since most stored procedures are < 4000 characters, it's really more trouble than it's worth to work out a mechanism to decrypt. So if you know you're decrypting stored procedures > 4000 characters, you'll need to go with dSQLSRVD.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

rrodriguez SSC Journeyman Points: 84 More actions · Answer 5

Actually i am using 2 databases... One database is the "original" and here i have all the sources of the stored procedures.. then from this i script the stored procedures in one file.... and then 'MANUALLY' i add the WITH ENCRYPTION option.. one by one... After this i run this script (with the WITH ENCRYPTION..) on the second database... and finally i script the stored procedures from the second database..

But this is very..very..very..hard..