Целевые таблицы SQL Server (2005) в разных схемах из процедуры хранения

Пожалуйста, может кто-нибудь посоветовать, возможно ли иметь хранимую процедуру в схеме [dbo] для выбора данных из одной таблицы в одной схеме для доступа пользователей через один роль базы данных и выбирать данные из одноименной таблицы в другой схеме для доступа пользователей через другую роль базы данных?

Например, если у меня есть три схемы в моей базе данных:

  • дбо
  • зеленый
  • красный

У меня есть два входа в базу данных [RedLogin] и [GreenLogin]. Они подключаются к моей базе данных, используя соответствующих пользователей базы данных [RedUser] и [GreenUser]. Эти пользователи являются членами соответствующих ролей базы данных [RedRole] и [GreenRole].

  • [RedUser] имеет схему по умолчанию [red].
  • [GreenUser] имеет схему по умолчанию [green].
  • [RedRole] имеет разрешение на выполнение в [dbo] и разрешение на выбор в схеме [red].
  • [GreenRole] имеет разрешение на выполнение в [dbo] и разрешение на выбор в схеме [green].
  • В схеме [зеленый] у меня есть таблица с именем [Пользователь].
  • В схеме [red] у меня есть таблица с именем [Пользователь].

  • В схеме [dbo] у меня есть хранимая процедура с именем [User_GetAll], которая выполняется

    ВЫБЕРИТЕ * ОТ ПОЛЬЗОВАТЕЛЯ;

Я бы хотел:

  • Для пользователей, которые входят в систему с помощью [Redlogin] и вызывают [User_GetAll], получают всех пользователей из [red].[User< /strong>] таблица.
  • Для пользователей, которые входят в систему с помощью [Greenlogin] и вызывают [User_GetAll], получают всех пользователей из [green].[User< /strong>] таблица.

Это вообще возможно? Если да, то как лучше всего это сделать, пожалуйста? Спасибо.

Обратите внимание: приведенный выше сценарий предназначен только для того, чтобы получить представление о том, чего я пытаюсь достичь. В реальном проекте есть много таблиц и хранимых процедур, к которым нужно будет применить решение.


sql-server schema stored-procedures role-based
person Dib    schedule 26.06.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Это должно быть возможно, если вы используете метод SCHEMA_NAME(), который возвращает схему вызывающего объекта по умолчанию (http://msdn.microsoft.com/en-gb/library/ms175068.aspx)

Итак, для вашего примера:

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

Обновление:

Как ни странно, когда я только что провел быстрый тест, вызвав EXEC('SELECT * FROM USER'), он использовал схему пользователей по умолчанию. Возможно, вы могли бы попробовать обернуть содержимое вашей хранимой процедуры внутри одного EXEC('')

Например:

EXEC('
    SELECT * FROM USER
    SELECT * FROM USER
    SELECT * FROM USER
    SELECT * FROM USER
')
person sWW    schedule 26.06.2014
comment
Это было бы правильным решением, но не предпочтительным, поскольку я бы не хотел преобразовывать все хранимые процедуры, где он применяется, для выполнения строки SQL, если это возможно. Очевидно, что если у меня нет другого выбора, то это было бы хорошим решением. Спасибо. - person Dib; 26.06.2014
comment
Хотя в моих тестах это все еще довольно неприятно, вы можете обойтись одним оператором exec. - person sWW; 26.06.2014
comment
Выглядит хорошо, но я действительно хочу избежать изменения всех моих SPROCS для выполнения SQL в очереди, так как их довольно много, а некоторые довольно длинные! Но оставлю это про запас! - person Dib; 26.06.2014
comment
На основании этого connect.microsoft.com /SQLServer/feedback/details/272964/ Думаю, вам не повезло. - person sWW; 27.06.2014
comment
Я думаю, вы правы. Похоже, мне нужно будет дублировать один и тот же Stored Proc для каждой схемы. Хороший шаблон против повторного использования кода, который будет! Программирование баз данных действительно является гражданином второго сорта, если вы привыкли программировать на современных языках, таких как .net. Немного жаль, что SQL Server не перешел в 21 век. - person Dib; 27.06.2014