Script encrypted stored procedures

spivey24

I brought this issue up in the past, but it was never really answered. We really need a way for SQL Compare to force the scripting of encrypted stored procedures like it did in version 4.x. I don't want it to actually compares the contents, but if it considered them different ALWAYS instead of ignoring them, this would help tremedously.

There is no harm in updating a strored procedure that didn't change, but having it skip a change, because the production version was encrypted causes lots of problems.

We have a situation where some stored procedures in the live environment are encrypted. We would like these to be updated with every release regardless of whether they changed. Currently, we have to run a compare against a blank database to get a script of every stored procedure, then update all stored procedures in the release.

Thanks.

Brian Donahue

Hello,

The handling of encrypted SQL Server objects hasn't changed much between version 4 and 5. In order to compare an encrypted stored procedure, SQL Compare has to decrypt it. There are two circumstances when it absolutely cannot; in SQL Server 2005 and when the user running the compare doesn't have a sysadmin or securityadmin role n SQL 2000. The trick of decrypting these objects requires high priviliges.

If the decryption fails, then SQL Compare shows 'the object is encrypted' instead of the stored procedure's code.

Hopefully this helps!

spivey24

In version 4, if it couldn't decrypt the sproc, it would mark it as being different and allow you to include the update in the script. This was good.

In version five you no longer have that option. This is bad.

Try it out.

It doesn't seem like a big difference, but when you have thousands of sprocs, some encrypted, its a huge deal.

Thanks.

Brian Donahue

Ah, you mean if the proc is encrypted in one database, but in the clear in the other database?

spivey24

Yes exactly. In the dev/stage environment the sproc would be clear, but on live it will be encrypted. In version 4 it would mark it as different (regardless if it actually changed) and allow you to script the update. In version 5, you can't do it.

Thanks.

Brian Donahue

I'm pretty sure the change was intentional. Since you can't script the encrypted procedure anyway, there's little point in highlighting it as a difference. Maybe in the future, there could be a third status: Different, Identical, and No Idea. That wouldn't inspire much confidence in the results, though! ;-)