Thanks to all of the many people responding to my request for feedback. It is helping me shape the direction for editorials next year. I especially appreciate the replies where English is not your first language. Your grammar was much better than mine. I’ll be humbled for quite some time.
One reply did pose a question regarding how to protect intellectual property in the form of the database design and code in an SQL Database. How do you keep people from reverse engineering your schema, procedures, triggers, and other proprietary work you have done? It’s not easy. Here are some of the things I have seen done.
1) Hide all of your design and implementation behind obfuscation. Normally, we create tables with names that mean something to us. We create functions, triggers and procedures with human readable names describing what they are, or what they do. Historically, because SQL is self-describing, some companies use meaningless codes for object names. Tables may be named something like T001, while containing Customer data. Even column names provide no information, having names like T001-001, meaning Column 1 in table 1. You need a magic decoder ring to understand it.
Imagine writing code where everything is named based on the type of object and the order in which it is created. You’d spend more time looking up table names and column names that writing code using it. For this reason, the code is generally created against a schema with meaningful names, and then the object names are altered as part of the packaging process.
To keep things hidden as much as possible, the database access code is also modified to use the new object names. A motivated hacker could probably decompose what is going on with some trace monitoring, etc. But, it would take a LOT of work.
This is like putting a lock on the door of your home. It will keep casual neighbors or strangers out. But, it won’t keep a professional thief out, or someone who doesn’t care about damage from crashing through the door.
2) The only other way you can keep someone from de-composing your database design and logic is to run on you own instance of SQL and not allow them to define any security information on the database instance. You could ship using SQL Express, install it as part of your application install, and configure security such that only your application has access to the SQL Server instance. That means that your application must handle all administrative tasks.
3) Another less secure method would be to use database schemas for things that a user may access, and keep those to the minimum permissions to make your app work. Those would be the only schema the users of the app could see. If you used all stored procedures for CRUD work, then none of your database schema is exposed. A different account, from your application, accesses the database using the DBO or some other schema, not used by the average user of the application. This other schema is used for backups, and applying database updates; those kinds of tasks.
There are a couple other ways that you might consider. They are more complicated, and probably better studied with a google search.
Cheers,
Ben