1

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.

Dib
  • 2,001
  • 2
  • 29
  • 45

1 Answers1

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
')
sWW
  • 585
  • 4
  • 13
  • That would be a valid solution, but not preferred as I would not really want to convert the allof the stored procs where his applies to execute a SQL string, if possible. Obviously if I have no other choice then this would be a good solution. Thank you. – Dib Jun 26 '14 at 12:28
  • Whilst still quite unpleasant in my tests you could get away with a single exec statement. – sWW Jun 26 '14 at 13:40
  • Looking good but I really want to avoid changing all my SPROCS to execute SQL in line as there are quite a few to do and some are quite long! But will hold this in reserve! – Dib Jun 26 '14 at 13:48
  • 1
    Based on this https://connect.microsoft.com/SQLServer/feedback/details/272964/using-default-schema-in-stored-procedures I think you are out of luck. – sWW Jun 27 '14 at 15:32
  • I think you are right. It looks as if I will need to duplicate the same Stored Proc to each schema. A nice anti-code reuse pattern that will be! Database programming really is a second class citizen when you are used to programming in modern languages like .net. It is a bit of a shame that SQL Server has not moved into the 21st century. – Dib Jun 27 '14 at 15:38