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} 
   SELECT * 
   FROM {table_name} 

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


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? :)

  • What do you mean "denies connection" please? – gbn Feb 14 '12 at 14:35
  • 1
    Can I ask why you think sharing tables in a single database is better? The reason I like the single tenant model is that if one customer gets big it's easy to move their database to another instance or filegroup than manually pulling all of their data. It's also much easier to implement security (as you've noticed) and some companies will actually not allow you to store their data in a heap with everyone else's... – Aaron Bertrand Feb 14 '12 at 14:36
  • 1
    I don't think so. But easier does not necessarily mean better anyway. YMMV and I don't know all of the factors that are going into your decision. I looked at the 6-year old article and couldn't correlate what you meant by "scenario 3." – Aaron Bertrand Feb 14 '12 at 14:42
  • 1
    In my previous job I managed a single instance with 500+ identical databases - not just one per customer but one per division. If you can script the change that applies to one customer, you can script it 500 times and run it in a loop. It really wasn't all that much different than single-database change management, except as an added bonus we could roll it out to different clients on a different schedule if we needed to (e.g. we had different SLAs and different maintenance windows for different clients). – Aaron Bertrand Feb 14 '12 at 15:18
  • 1
    @erizias yes, IMHO the designers are horrible, full of bugs, slow you down and contribute to even more bad habits. DDL FTW. I find it much faster to whip out an ALTER TABLE command than it takes for the designer to load. – Aaron Bertrand Feb 14 '12 at 20:03

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

  • Ah great, thanks! And the multitenancy-part has been done correct? – Behrens Feb 14 '12 at 14:56
  • 1
    @erizias: Using SID, yes. I'll let Aaron discuss the rest with you :-) I've never had to do it, so never thought about it much – gbn Feb 14 '12 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.