Encyrption in SQL2000 1

[Warnie]

Hi,

I had an encypted SP in SQL7.0. I decrypted the same and now I have ported it to SQL2000. Now I want to encypt it again. I put a "WITH ENCRYPTION" clause to the same and saved it. On opening the SP, I should be able to see the Exe(Binary characters) but instead all I am getting is a SQL-DMO error Error 20585: [SQL-DMO]/****** Encrypted object is not transferable, and script can not be generated. ******/.
Why is this so? Please note that I have just migrated from SQL7.0 to SQL2000. Is this a problem with SQL2000?

Thanks,
Warnie

 

[tlbroadbent]

Not a problem. It is a feature. The only reason to encrypt stored procedures is to prevent someone from viewing the code. Hope you saved a non-encrypted version of the SP. There are procedures available to decrypt an encrypted SQL 2000 SP. I can't recall the locations but you should be able find them via a web search. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[osjohnm]

Warnie

Have a look at the following thread:

thread183-343538

This person also had to decrypt a stored procedure. In my post you will find SQL script the will create a stored procedure to decrypt encrypted procedures. I found this on the net(as Terry mentioned) awhile back.

Hope this helps

John

 

[Warnie]

Based on the information I have got, I am summarising as follows:

1.In SQL 7.0, the WITH ENCRYPTION clause will encrypt the SP. The SP on being viewed will show up as

"Exec((0x20299....

2.In SQL 2000, using the WITH ENCRYPTION clause will encrypt the SP and when viewed will throw up this message
Error 20585: [SQL-DMO]/****** Encrypted object is not transferable, and script can not be generated. ******/.

It is strange that an error is being returned. That was the only concern that I am getting an error instead of the SP showing itself as "Exec((0x20299....

Is my conclusion right?

 

[Warnie]

Forgot to add this:

tlbroadbent,

You have suggested that there are procedures available to decrypt the 2000 SP. But when I can't view the binary code, how can I decrypt a SQL 2000 SP? Please note that the server is throwing an error
Error 20585: [SQL-DMO]/****** Encrypted object is not transferable, and script can not be generated. ******/.
I guess the only way is to have a decrypted version before encrypting. Is this right?

 

[osjohnm]

Warnie

The reason that the "error" comes up in SQL 2000 is because the procedure is encrypted. The reason for encrypting a procedure is so that when people try and open the procedure code they get an error and can't see the code. Thats the idea of encryption.

It is always a good idea to keep an original unencrypted copy of your procedures. Go to Thread183-343538, copy the sql script that creates sp_decrypt_sp, execute it in query analyser against your database. Then execute it by passing in your procedure name:

exec sp_decrypt_sp 'yourprocedure'

Then go back to enterprise manager and open the procedure. You should be able to see the code and not receive an error.

John

 

[Warnie]

Thanks John, that was really helpful!!