May 18, 2010 at 5:25 am
Hi,
We have used 'Shared database and separate schema' approach under Multi-tenant database architecture.
We are facing issue while creating the different schema for multiple Tenants. My question is : do we have to create separate stored procedure as well, other than tables.
For example :
I have a default schema as Default.tbl_Name whose user and login is :sa and under which I have already created all the Stored procedure as : Default.usp_StoredProcedure.
For Tenant1, we create schema as Tenant1.tbl_Name, having Login as :Tenant1 and User as : Tenant1
For Tenant2, We create schema as Tenant2.tbl_Name, having Login as :Tenant1 and User as : Tenant1
Now for Tenant1 and 2 I don't want to create separate Stored procedure under their Schema's as that would increase the number stored procedure in the shared database. And I want my program via Tenant1 or Tenant2 login to access the Stored Procedure of "Default" schema and still access the table data of Tenant1 and Tenant2.
Please advise how this can be done in SQL server 2008, your any help would be appreciated.
May 18, 2010 at 5:34 am
A question from what i have understood from your question. 🙂
When both users can access each others data then why do you need different sachems?
Nag.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 18, 2010 at 6:22 am
I am sorry for the confusion. Each User will only access their own schema and its data, hence no user can access schema of any other user. Whereas the Default user has the access to all schema which is : 'sa' in our case.
May 18, 2010 at 6:34 am
ASsuming I understand what you're asking, you want to have a single query that will hit the appropriate schema depending on the user that is making the request? The only way you can do that is through dynamic sql...I think... Let me try a little experiment...
Nope. I tried cheating, leaving off the schema and creating identical tables on different schema's that then are defaulted to a given login... it doesn't work.
You either need to create specific procedures or you need to work with dynamic SQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2010 at 11:01 am
Thanks for the reply..so it means either I have create Schema specific Stored Procedure..For example : Tenant1.usp_GetCustomer and Tenant2.usp_GetCustomer or Create dynamic sql which will take Schema name as Parameter...right?
May 18, 2010 at 11:05 am
Pretty much. There may be another way around, but I don't know it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2011 at 10:14 pm
I have a similar scenario - I am have multiple clients that need access to a common database and they have their own separate tables - so I plan to use shared database and separate schema. Between using separate stored procedures and building dynamic SQL with schema name based on the user - which one is better based on performance? Kindly give your thoughts on this.
September 29, 2011 at 4:45 am
setvij (9/28/2011)
I have a similar scenario - I am have multiple clients that need access to a common database and they have their own separate tables - so I plan to use shared database and separate schema. Between using separate stored procedures and building dynamic SQL with schema name based on the user - which one is better based on performance? Kindly give your thoughts on this.
You'll get more and better responses by starting your own thread instead of posting a question in another thread. The only people who will see this question are those that are subscribed to this one.
Which is better for performance? Usually having stored procedures or parameterized queries are the best for performance. Dynamic SQL generally means lots of compiles, very poor plan reuse, memory issues, and difficulty in fixing poorly performing queries. In general, stored procs are better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply