Best practice advice please

I'm looking for advice on the best way to stop stored procedures and CLR assemblies from being

copied from their originally installed server to a different server, for the same company or even copied

to another company.

Are there established ways for achieving this level of protection.

Also, I was hoping that encrypting stored procedures would be a 100% reliable way to stop

malicious copying of the code. But I have read that this is not the case. Any advice in this

area would also be appreciated.

Thanks

Steve

Procedure encryption, like the locks on the doors to your home/abode, are only designed to keep out the 'merely curious' -not those determined to be inside.

It is very difficult to prevent a dba (sysadmin) from accessing the code from stored procedures and assemblies. Easily obtainable 'decryption' tools will allow you to see the the code in unencrypted form -even if the code is from an obfuscated assembly (SQL 2005).

Best Practice is to control who has sysadmin access to the servers, all other users have finely 'tumed' permissions, and have a well crafted 'acceptable use' policy in place.

|||

Thanks Arnie.

In my using of Profiler, I could not see the code for encrypted SPs, so I am surprised to hear that the code from an obfuscated assembly can be seen.

Any how, I'll have to think some more.

Thank you

Steve

|||Well the assembly itself cannot be seen and the statements neither, it will only by the following visible in Profiler:

-- Encrypted text

But in addition to Arnie, the "encryption" in SQL Server 2005 is weak and not build for hiding the procedure code in a secure manner. It is just to add an additional barrier to prevent the users to easily read the procedure code. it is doable to read the code but needs additional knowledge about decryption of the procedure code (or even just a good experience to handle the google search :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de