I am writting a multitenancy application which I would like to have schema level seperation. I have written the application now and I am starting on the part that will create a new user, schema, login and connection string. The idea is that when someone
logs in to the application their session object will have their schema specific connection string.
I have worked on creating a new user for a specified schema but I am struggling to lock that schema to that user, currently when I create a user for a specific schema they can view all other schemas, which is not what I want. I have found that you can deny
access to a schema but once I have 100 schemas this will become a heavy process.
Is there a way to say 'deny this user access to all schemas' and then 'grant this user access to this one schema' please?
UselessChimp
Member
226 Points
115 Posts
Schema level Multitenancy
Oct 15, 2012 04:20 PM|LINK
Hello everyone once again,
I am writting a multitenancy application which I would like to have schema level seperation. I have written the application now and I am starting on the part that will create a new user, schema, login and connection string. The idea is that when someone logs in to the application their session object will have their schema specific connection string.
I have worked on creating a new user for a specified schema but I am struggling to lock that schema to that user, currently when I create a user for a specific schema they can view all other schemas, which is not what I want. I have found that you can deny access to a schema but once I have 100 schemas this will become a heavy process.
Is there a way to say 'deny this user access to all schemas' and then 'grant this user access to this one schema' please?
Chen Yu - MS...
All-Star
21814 Points
2513 Posts
Microsoft
Re: Schema level Multitenancy
Oct 22, 2012 09:23 AM|LINK
Hi,
Did the new user only have connect right and have no granted access to any other schema, including [dbo]? Please make sure of this point.
I found this thread which is about permission of Schema. It may help you to build your schema level.
http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas
Thanks,
Feedback to us
Develop and promote your apps in Windows Store
UselessChimp
Member
226 Points
115 Posts
Re: Schema level Multitenancy
Oct 23, 2012 05:15 PM|LINK
Thank you very much for this. The link was very informative.