SQL Server (2005) target tables in different schemas from store procedure

Question

Please can any one advise if it is possible to have a stored procedure in the [dbo] schema select data from one table in one schema for users accessing via one database role and for it to select data from a like-named table in another schema for users accessing via another database role?

For example if I have three schemas in my database:

• dbo
• green
• red

I have two database logins [RedLogin] and [GreenLogin]. These connect to my database using respective database users [RedUser] and [GreenUser]. These users are members of the respective database roles [RedRole] and [GreenRole].

• [RedUser] has a default schema of [red].
• [GreenUser] has a default schema of [green].
• [RedRole] has execute permission on [dbo] and select permission on the [red] schema.
• [GreenRole] has execute permission on [dbo] and select permission on the [green] schema.
• In the [green] schema I have a table called [User].
• In the [red] schema I have a table called [User].

• In the [dbo] schema I have a stored procedure called [User_GetAll] that runs

  SELECT * FROM USER;

What I would like is:

• For users who login with [Redlogin] and call the [User_GetAll] get all users from the [red].[User] table.
• For users who login with [Greenlogin] and call the [User_GetAll] get all users from the [green].[User] table.

Is this even possible? If so what is the best way to achieve it, please? Thank you.

Please note: The scenario above is just to get a flavour of what I am trying to achieve. In the real project there are many tables and stored procedures that the solution will need to be applied to.

Answer 1

This should be possible if you use the method SCHEMA_NAME() which returns the default schema of the caller (http://msdn.microsoft.com/en-gb/library/ms175068.aspx)

So for your example:

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'SELECT * FROM ' + SCHEMA_NAME() + '.USER'
EXEC sp_executesql @Sql

Update:

Oddly when I just did a quick test, calling EXEC('SELECT * FROM USER') it used the users default schema. Perhaps you could try wrapping the contents of your stored procedure inside a single EXEC('')

For example:

EXEC('
    SELECT * FROM USER
    SELECT * FROM USER
    SELECT * FROM USER
    SELECT * FROM USER
')