I've a few thoughts on software piracy.
Web-based database-driven application inherently requires non-trivial infrastructure for the average geek, including database, web server, machine with internet connection as well as domain name registration. The average geek i referred to is typically a IT technician/programmer who has access to an existing installation of the web-application. IMO, these infrastructure requirements makes web-based database driven application less vulnerable to software piracy because it requires non-trivial resources to set up and run.
In addition to copying a bunch of files, the average geek also requires access to database structures supporting the application. Assuming that the average geek is able to meet the aforementioned infrastructure requirement as well as having the access right to access the application database, this means they will be able to copy and redistribute the application at his/her will. To address this particular scenario, i was wondering if SQL Server or any other RDBMS is capable of precompiling stored procedures into binary format and allowing only installation of these binary, thus disallowing the average geek in reconstructing the required stored procedures.
Just my thoughts. tell me what you think.
Stored procedures can't be precompiled into a binary, nor are they compiled into a binary when they are run. The RDBMS creates an execution plan based on the SQL code and a number of database variables including, but not limited to: the size of the tables being queried, what indexes are on those tables, the current index statistics, what type of indexes (clustered/non-clustered), the number of joins in the query, the type of joins (inner, outer, etc), the join technique (nested loop, merge, hash), the order of the joins, the estimated size of the data that will be returned, the number of cpus on the server, and probably the position of moon as well as the entrails of several small birds. You get the picture.
By using a stored procedure instead of directly sending SQL to the database, you can cache the execution plan instead of creating it on the fly every time you run the query. You should refresh the execution plans from time to time since things like table size are always changing. How often you need to depends on how much the database is used. To do that you can run sp_recompile on the sp or add WITH RECOMPILE to the sp to keep it from caching the execution plan so it has to be recreated every time it's run.
So you might be wondering why they call it recompile. Maybe they figured most people using a database are familiar with programming so it would make more sense to them. Or maybe they figured sp_DropTheCurrentExecutionPlanInTheCacheAndCreateANewOne was too much to type. It is more than just semantics though. There are also internal events in SQL Server that can trigger the recreation of the execution plan, and the original SQL code is needed to do that.
If I understand correctly, you want to avoid people walking off with your Stored Procedure code. If that's the case, then you can use the WITH ENCRYPTION option to create your stored procedure.
CREATE PROCEDURE dbo.TestEncryption
-- DO SOMETHING
If the user/developer tries to view the contents (sp_helptext, etc.) they will get the message:
The object comments have been encrypted.
If you store your SQL script as a binary file (or simply encrypt it) you could then avoid prying eyes (unless someone is smart enough to Profile your install).
"Encrypted" stored procedures aren't really. There are several utilities and stored procedures you (and your pirate) can download to decrypt them.
From previous experience: if one tries hard to fight off piracy using encryption, keys and the like, all one gets is a product that is sometimes less efficient, usually harder to maintain, and almost certainly cracked. So... my advice is to take piracy as a recognition of a good product, and move on.
True, but I think his audience is a basic developer or user. It doesn't take a genius to run Profiler and see the SQL Script install either. You'd see all the raw code as the procedures were created.
I think most decent developer's would eventually figure out what changes in the database when a proc fires off anyhow. I definitely agree with the maintenance issue though.
If it's a packaged application that shouldn't be a problem. In fact, it would probably deter some developer's from messing with the procedure. Which would be a good thing.
Encryption is like a car alarm or a bike chain; a good deterent, but not going to stop the determined. Somehow I don't think that will ever change...
I agree to some extent, if your stuff is pirated then you probably have a good product. Gotta take the good with the bad. I just want the bad to have to work a little for it...