Retrieving data from SQLServer mutitenant database

Question

I have a multitenant database with something like the following naming structure:

Sch1.Logins => 2 records
Sch2.Logins => 4 Records

Now I want to be able to "Execute as" different users , say when user1 tries to run the query "select * from Logins", the results should be returned from the Sch1.Logins (2 records) and when user2 runs the same query, it would return records from Sch2.Logins.

1. How can I do this?
2. What settings are required for each user?

Answer 1

Set the default schema for the users. You can see this in the user properties window through SQL Server Management Studio.

Or script:

ALTER USER user1 
WITH DEFAULT_SCHEMA = Sch1;

Answer 2

The following method seemed to work for me. Created a new user and associated it with a login. Then kept associating the relevant user different schemas.

 Use *MyDatabase*

Create User test2
For 
Login *myLogin* 
With Default_Schema = S1001 -- This is to map the created user to login

ALTER AUTHORIZATION ON SCHEMA::S1001 TO test2;

EXECUTE AS USER = 'test2'
select count(*) from Login

REVERT

ALTER AUTHORIZATION ON SCHEMA::S1001 TO dbo;

ALTER USER test2 WITH DEFAULT_SCHEMA = S1002
ALTER AUTHORIZATION ON SCHEMA::S1002 TO test2;

EXECUTE AS USER = 'test2'
select count(*) from Login

Revert