Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

Question

I am eagerly trying to design the perfect multitenant data architecture for my database.

I am getting inspiration from an article posted by Microsoft, based on scenario 3.

I have been given all my tables a tenant-column, and afterwards created a matching VIEW

ALTER TABLE {table_name} add TenantID varbinary(85) DEFAULT SUSER_SID()

CREATE VIEW {view_name} 
AS 
   SELECT * 
   FROM {table_name} 
   WHERE TenantID = SUSER_SID()

Each of my clients has their own database user account, these users has been given following rights:

db_datareader
db_datawriter
public

And then I changed the following:

DENY SELECT ON {table_name} TO {user_name}
GRANT SELECT ON {view_name} TO {user_name}

Anyway, am I missing something for now? :)

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

I suspect it is how SSMS enumerates tables

However, you don't need DENY. Just don't GRANT.

Best practice would be something like this

Remove membership of the db_datareader and db_datawriter role
(I never use them)
Create a specific role (with CREATE ROLE) and GRANT on the view to this.

Then, you don't need to DENY on the table because it won't be checked anyway due to "ownership chaining"

Saying that, I'd also use SCHEMAs so I don't even require GRANT to the view. GRANT once to the SCHEMA. For more, see

Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

1 Answers1

Linked