2

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?

2 Answers 2

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;
2
  • 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, 2014 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, 2014 at 7:38
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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