An interesting question came up on the MSDN SQL Forums yesterday about best practices for applications to access their databases on SQL Server:

Here's a good article on SQL Injection by one of my favorite authors, Michael Coles: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/.

Note that most of the SQL Injection articles will be filed under the "Dynamic SQL" heading.

I have implemented large shared schema, multi-tenant applications & databases before and it is suprisingly straight-forward as long as you follow certain rules:

1) All application users must be easily distinguishable by tenant to SQL. 

 Practically speaking this means that either each tenant has a separate Login or each user has a sepearate Login to SQL Server and a Tenant_Users table to map the user back to their tenant association.

2) Every table that the application can write to must have a tenant_id column that identifies the tenant-owner of the data.

3) Every such table must have a corresponding "security" view that insure that any user can only access rows in that table from the same tenant., like so:

CREATE VIEW Secure_TABLE as
 Select * 
  From Physical_TABLE T
   Inner Join Tenant_Users U
    ON U.tenant_id = T.tenant_id
     And U.UserName = sUser_sName()

4) No application code, including client code, application stored procedures, views, etc., is permitted to directly access the physical tables.  All such data access is only permitted through the Security views.  (use database roles, schemas and permissions to implement this, do not rely on code).

If you notice, this approach insures that, except for the user Logon's, the application has no knowledge of the multi-tenancy, and no application changes should normally be necessary.  This means that you can move a tenants data into or out of this approach without any changes to most applications, other than changing the Server.Database address. (note that to be truly transparent you would have to remove the tenant_id from the output of the security views).

The tenant_id should be made the first field of the primary key and probably also the Clustered Index (if different).

I've been invovled in some recent discussion on Multi-Tenenacy databases and in particular, the Shared Schema implementation:

http://www.sqlservercentral.com/Forums/FindPost610335.aspx
http://www.sqlservercentral.com/Forums/Topic529170-361-1.aspx
http://www.sqlservercentral.com/Forums/Topic569567-149-1.aspx

Multi-Tenancy databases are databases with multiple customer organizations ("tenants") all running the same application and all using the same database server.  The three stadard approaches to this are:

1. Seperate Databases
2. Same Database, Seperate Schemas
3. Shared Schemas

In the last one, all of the tenants use the same tables, but a tenant_id is added to each table to distinguish them.  The is an MSDN article that explains this (http://msdn.microsoft.com/en-us/library/aa479086.aspx)

As it happens I designed and implemented a large Shared Schema Multi-Tenant DB several years ago, and  learned many insights from that.

Way back in March I answered question from two posters about the notorious Two-Hop Rule here: www.sqlservercentral.com/Forums/Topic471172-359-1.aspx#bm473248.  The thread has a good summary of the problem and cause by me, and an excellent post by Brian Kelley on how to use Kerberos to fix it.

A helpful tip from Microsoft MVP Gail Shaw here on how to bypass a bad Logon Trigger:

Connections via the DAC don't fire login triggers. From management studio, open a new query and specift admin: before the server name (admin:MyServerName) and use windows authentication. You must be sysadmin.

If remote DAC hasn't been enabled (the default), you will have to do that from a querying tool on the server itself. If the server doesn't have management studio, you can use SQLCMD. Specify the -A switch

Once in, you can disable the trigger.

DISABLE TRIGGER MyBrokenLoginTrigger ON ALL SERVER

Be very, very careful when writing login triggers. If, for any reason, the trigger fires an error of Sev 16 or higher (object does not exist, database not found, permission denied) the trigger fails and rolls back the login.
I've had a couple panicked, late-night phone calls because of these.

Something that I have wanted to do for a while is to write a Logon Trigger for SQL Server.  This bit of code from BOL demonstrates how:

CREATE TRIGGER connection_limit_trigger
 ON ALL SERVER WITH EXECUTE AS 'login_test'
 FOR LOGON
AS
 BEGIN
  IF ORIGINAL_LOGIN()= 'login_test'
   AND (SELECT COUNT(*) FROM sys.dm_exec_sessions
	WHERE is_user_process = 1
	AND original_login_name = 'login_test') > 3
  ROLLBACK;
 END;

Next up: how to bypass a bad Logon Triger with the DAC (Dedicated Adminstrator Connection).