Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 2

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;
share|improve this answer
    
Thanks for the response. But unless we set the owned schema for the user, it doesnt seem to work. Why would that be? –  Farax Jun 30 at 4:25
    
Shouldn't need to set the owner. They just need permission to read the tables in the schema. –  Rob Farley Jun 30 at 7:38

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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