The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.
Below is an example that will explain you in detail
use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';
use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go
-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go
create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go
Now test :
--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A
-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Now create Stored Procedures :
CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
select * from schemaA.TableA
Now Grant execute permissions to UserA on schemaB's SP
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go
Test it .. to see if UserA is able to run SP from schemaB. This will PASS
EXECUTE AS LOGIN='UserA';
Exec SchemaB.proc_SelectUserB;
revert;
go
--- Kin-A
But UserA wont be able to see data from SchemaB
EXECUTE AS LOGIN='UserA';
select * from SchemaB.TableB
revert;
go
--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:
EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go
Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc
role and they will inherit all the permissions granted to that role.
GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go
If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go
This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.
Below will explain the permission hierarchy :
I would recommend to use log shipping but only if you do not want to prevent delete operations. Log shipping keeps the target database the same as the source database.
Log shipping is a relatively simple mechanism, it just backups and copies over the transaction log and applies it to the target database. It is a bit simpler to setup and to maintain than Replication.
Replication also keeps the source database completely in sync with the target database. With transactional or snapshot replication there is no possibility to exclude delete operations.
The only exception is that in merge replication exclusion of DELETE statements is still possible. However, this possibility will be removed in future versions of SQL Server (see the first note in https://msdn.microsoft.com/en-us/library/ms146947.aspx). So that is nr. 1 of your worries I think. Some more considerations can be found here: https://technet.microsoft.com/en-us/library/ms151206(v=sql.105).aspx.
Performancewise there also can be a big impact. This depends a lot on the database and the operations on it. So test it in a test environment to see if the performance loss is acceptable.
Personally, I'm not a huge fan of merge replication as I have experienced many problems with it.
Summarizing, if you want to prevent delete operations I would recommend to write your own synchronization mechanism. If you don't want to do that, and can live with the issues mentioned above, merge replication will also be usable.
Best Answer
Turns out Cross Database Chaining was the answer (for us at least). Turning this on allowed me to give the user limited (dataread/datawriter) access to the database that only shows their data, and NOT the underlying database that actually stores everyone's data.
I appreciate the suggestions - thanks for weighing in.
As an aside - the reason I don't like using schema's is due to generally wanting to have each be assigned a default schema, and not have to qualify every table name with the schema name.
I find that for testing purposes, constantly having to either update my default schema (to simulate working as different users), or actually connecting as lots of different users who already have preset default schemas ends up being more of a headache than just logging in with Windows Auth as me (always) and just connecting to different databases to get different user's data.
This also allows me to (conceptually) have an entire database for each user - which really cleanly and nicely isolates their data.
Data liberation can then be achieved by literally just SELECT * FROM each table in the user's database.
Also - from my testing, of separating by schema or database (in SQL 2012) made no difference that I was able to detect across many hundreds of thousands of a variety of different types of operation.
If someone knows of some performance issue I'm not aware of - I'd love to know about it, but... at this point, keeping each customer's data in their own database is the right answer for us.
Thanks again. Good night.