Another quick and dirty SQL solution.

We had a need to take an existing app and make it so we could run it for multiple owners, as though they each had their own installation, but we didn't want to make a lot of code changes and we didn't want to actually have it connect to different databases because we make schema changes as we mod the app fairly frequently.

This is what I came up with: Use "Application Name" in the connection string as a filter key and make VIEWs that filter on APP_NAME() that the application will use instead of the real table.

Rename the tables that need to vary by owner.
exec sp_rename 'mytable', 'mytable_byappname'

Add a column to record the owner.
ALTER TABLE mytable_byappname ADD appname varchar(255) DEFAULT App_Name()

Set that column to the first owner
UPDATE mytable_byappname SET appname='hafthor.com'

Add that new appname column to your primary key if you are using a natural key (so that the same natural key can be used across owners)

Add that new appname column to your indexes (before other columns) if you think you'll need it.

Add a new index on just this new appname column, so the view will be fast (as long as the number of records for any given owner doesn't represent more than say 10% of all records).

Create a view that filters by owner
CREATE VIEW [mytable] AS SELECT * FROM [mytable_byappname] WHERE appname=App_Name()

Now, just add 'Application Name=hafthor.com' or whatever to the connection string.

Consider making the Application Name really short.

CREATE TABLE audit(
  [on] datetime not null default getutcdate(),
  [by] varchar(255) not null default system_user+','+AppName(),
  was xml null,
  [is] xml null
)

CREATE TRIGGER mytable_audit ON mytable for insert, update, delete as
INSERT audit(was,[is]) values(
  (select * from deleted as [mytable] for xml auto,type),
  (select * from inserted as [mytable] for xml auto,type)
)

The main goals were to make something really quick to record who did what when across several tables, but it is presumed that this would rarely get used, so queryability wasn't paramount. It would be more likely that tables get created or altered than this stuff would be dived into.

It doesn't work on tables that have certain kinds of fields that are incompatible with FOR XML like TEXT. Had one table with that and I just converted it to nvarchar(max) rather than fight it.

Also, this frees me from having to bloat my tables with fields like CreatedDateTime. Better still, I got to drop fields like DeletedDateTime which implied they shouldn't normally be selected, so every UPDATE and SELECT had to include WHERE DeletedDateTime IS NULL. This really caused problems since I could very easily get primary key violations because a record got 'DELETED' and recreated with the same natural key. I know, I know, just another argument for "surrogate keys only".

Wanted a generic way to pass a DataTable as a parameter to SqlCommand as a TVP (Table Value Parameter). It automatically creates the, IMHO, superfluous User-Defined Table Types. Here's what I have so far:

<Runtime.CompilerServices.Extension()> _
    Sub AddTableParameter(ByVal cmd As SqlCommand, ByVal n As String, ByVal dt As DataTable)
        Static typeXlat As Dictionary(Of System.Type, SqlDbType)
        If typeXlat Is Nothing Then
            typeXlat = New Dictionary(Of System.Type, SqlDbType)
            typeXlat.Add(GetType(Int64), SqlDbType.BigInt)
            typeXlat.Add(GetType(Int32), SqlDbType.Int)
            typeXlat.Add(GetType(Int16), SqlDbType.SmallInt)
            typeXlat.Add(GetType(Byte), SqlDbType.TinyInt)
            typeXlat.Add(GetType(SByte), SqlDbType.TinyInt)
            typeXlat.Add(GetType(Boolean), SqlDbType.Bit)
            typeXlat.Add(GetType(String), SqlDbType.VarChar)
            typeXlat.Add(GetType(Byte()), SqlDbType.VarBinary)
            typeXlat.Add(GetType(DateTime), SqlDbType.DateTime)
            typeXlat.Add(GetType(DateTimeOffset), SqlDbType.DateTimeOffset)
            typeXlat.Add(GetType(Char), SqlDbType.Char)
            typeXlat.Add(GetType(Single), SqlDbType.Real)
            typeXlat.Add(GetType(Double), SqlDbType.Float)
            typeXlat.Add(GetType(Decimal), SqlDbType.Decimal)
            typeXlat.Add(GetType(Guid), SqlDbType.UniqueIdentifier)
            typeXlat.Add(GetType(Xml.XmlDocument), SqlDbType.Xml)
        End If

        'assembly the typename and spec
        Dim tabletypedef As New List(Of String)
        Dim parm As New SqlParameter(n, SqlDbType.Structured)
        parm.Value = dt

        For Each c As DataColumn In dt.Columns
            Dim s As SqlDbType = SqlDbType.Variant
            If typeXlat.ContainsKey(c.DataType) Then s = typeXlat(c.DataType)
            tabletypedef.Add(c.ColumnName + " " + s.ToString)
        Next
        Dim tabledef = String.Join(",", tabletypedef.ToArray)
        Dim typename = "autotype_" + tabledef
        parm.TypeName = "[" + typename + "]"

        'create the type if needed
        Using cmd2 = New SqlCommand("select count(*) from sys.types where is_table_type=1 and name='" + typename + "'", cmd.Connection)
            If cmd2.ExecuteScalar() = 0 Then
                cmd2.CommandText = "create type [" + typename + "] as table(" + tabledef + ")"
                cmd2.ExecuteNonQuery()
            End If
        End Using

        'add the parm referencing the typename
        cmd.Parameters.Add(parm)
    End Sub