Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
Добрый день!
Sql server 2005. В базе DB1 есть 2 таблицы у разных владельцев: owner1.table1 и owner2.table1.
В базе DB2 есть хранимая процедура под владельцем dbo, которая должна брать данные из таблиц [DB1].[owner1].table1 и [DB1].[owner2].table1 в зависимости от того, под каким владельцем она выполняется. Соответственно пишу в ХП:
select * from [DB1]..table1 и получаю ошибку:
Недопустимое имя объекта "[DB1]..table1", хотя я запускаю процедуру от имени, например, owner1.
Если создавать ХП под владельцем owner1, то такой синтаксис работает.

Подскажите, есть ли какая-то возможность получать данные из таблиц owner1.table1 и owner2.table1 через процедуру, без указания владельца?
Заранее спасибо!

PS: понимаю что такая организация данных не оптимальна, однако этим занимается другая программа, я ничего тут уже не могу изменить.
15 янв 14, 10:56    [15417198]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
execute as caller? хотя не факт, что execute as был в 2005м, и не факт, что поможет с компиляцией.
15 янв 14, 11:08    [15417295]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
Glory
Member

Откуда:
Сообщений: 104760
execute as caller - это значение по умолчанию
А тс-а как раз наверное другая опция задана для процедуры, as owner, например
15 янв 14, 11:18    [15417357]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
в процедуре динамику в формате:

'select * from  [DB1].['+ user_name() +'].table1'
15 янв 14, 11:31    [15417434]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
dec0rator,

так и создайте тогда две процедуры: со схемами owner1 и owner2. в процедуре dbo вызывайте соответствующую. ну, еще вариант - селект в динамику завернуть.

execute as, владельцы и выполняющие пользователи тут вообще не причем. без указания владельца таблица ищется сначала в той же схеме, что и схема процедуры, потом в dbo.
15 янв 14, 11:33    [15417451]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Еще можно сделать 2 локальные вьюхи, под разными владельцами, которые будут указывать на соотв. таблицы в первой базе.
15 янв 14, 11:34    [15417456]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
Нужно наверное поподробнее раскрыть вопрос:
Существует система, которой изначально принадлежит база. Она ее контролирует(создает, редактирует, удаляет записи и тд). Также существует дополнительная надстройка, которая делает множество разных отчетов из этой базы. Хочется вытащить ХП, которыми пользуется эта надстройка в отдельную базу. И из этих ХП создавать отчеты, используя базу основной системы.

Проблема в том, что вся структура базы этой системы построена таким образом, что одинаковые по структуре таблицы дублируются для разных владельцев (это касается ВСЕХ таблиц этой системы). Речь идет о большем количестве таких таблиц и большом количестве ХП. Именно поэтому и возникла проблема.

1) Execute as все равно не меняет именно схему, только владельца для проверки прав как я понял. По умолчанию идет As Caller. Вместе с динамикой 'select * from [DB1].['+ user_name() +'].table1' конечно будет работать. Однако строить все ХП в таком духе - будет не очень то здорово (этих ХП очень много).
2) Ennor Tiegael, на данный момент используется именно такой вариант: две процедуры со схемами owner1 и owner2. Однако таких процедур увы очень много, и очень надоело их копипастить. Хочется найти какой то универсальный вариант.
15 янв 14, 13:01    [15418108]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Однако таких процедур увы очень много, и очень надоело их копипастить.

напишите процедуру, которая будет это делать. не придется копипастить вручную.
то есть, каждое создание/изменение процедуры - это будет:

alter proc dbo.ProcName as
...
go
exec CopyProcForAllUsers 'ProcName'
15 янв 14, 13:35    [15418336]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
aleks2
Guest
select * from [DB1]..table1

БЕЗ процедуры работает?
15 янв 14, 14:33    [15418812]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
daw, спасибо, попробую этот вариант

leks2, да, без процедуры работает

А каких-либо встроенных средств, чтобы установить текущую схему - нет?
15 янв 14, 14:56    [15419012]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
aleks2
Guest
dec0rator
daw, спасибо, попробую этот вариант

leks2, да, без процедуры работает

А каких-либо встроенных средств, чтобы установить текущую схему - нет?


with execute as caller
15 янв 14, 16:31    [15419902]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
aleks2 as caller по умолчанию идет...не работает так
15 янв 14, 16:43    [15419997]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
aleks2
Guest
dec0rator
aleks2 as caller по умолчанию идет...не работает так

С какой радости умолчание?
По умолчанию процедура исполняется под правами владельца схемы. Т.е. dbo, в вашем случае.
15 янв 14, 17:08    [15420239]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
aleks2,

с какими-такими правами владельца схемы? всегда было с правами вызывающего + цепочки владения.

и еще раз: в процедуре, если не указана схема, таблица ищется в схеме процедуры и потом в dbo. все.
ну проверить же можно - лень что ли?
15 янв 14, 17:12    [15420266]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
daw, вот я и не могу понять почему если я вызываю SELECT отдельно, он делает все как надо, а через ХП - не делает.
Т.е. захожу, например, в SQL managment studio под owner2, пишу:
Use DB2
select * from [DB1]..table1
все отлично.
Захожу под sa: получаю ошибку (Недопустимое имя объекта "[DB1]..table1")
Захожу под owner2, запускаю ХП с тем же запросом (по идее она запускается with execute as caller, который в данном случае есть owner2) и получаю ошибку. Почему?
Конечно методом создания процедуры, копирующей исходную можно добиться желаемого результата (по сути все делается одной строчкой), но хотелось бы до конца разобраться...а вдруг можно все-таки как-то запускать ХП чтобы она работала в контексте и в схеме какого-либо owner.
15 янв 14, 23:56    [15422140]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
dec0rator
а вдруг можно все-таки как-то запускать ХП чтобы она работала в контексте и в схеме какого-либо owner.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d3ccb3a9-1e91-4162-8252-9c60dc0b9e25/default-schema-resolution-doesnt-work-within-stored-procedure
An object reference in a stored procedure always refers to the same object, as determined by the owner of the stored proceedure. The user's default schema has no importance.

This is by design. To wit, a stored procedure has a single entry in the cache (if we overlook different plans due to SET options). Else there would have to be one plan for each user, if each user has its own default schema (as always was the case in SQL 2000 and earlier). That would be bad.

You mention that you have a multischema design. Schemas are mainly intended to be namespaces, not to be containers for multiple instances of the same data model. I have heard of poeple who have done this before, although I am not sure that I fully understand. Sometimes people duplicate schema across databases, which makes sense since since different customers using the same application prefers to be isolated. But I am not sure that just using multiple schemas is a good solution to that requirement.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
16 янв 14, 00:17    [15422194]     Ответить | Цитировать Сообщить модератору
 Re: Обращение к таблицам из процедур без указания схем  [new]
dec0rator
Member

Откуда:
Сообщений: 13
invm, спасибо за ссылку, все стало понятно.
С вопросом ясно, пойдем по пути динамического SQL я думаю (ну или может быть копирование самих процедур вспомогательной процедурой на разных владельцев).
Всем спасибо за помощь! Думаю тему можно закрывать.
17 янв 14, 09:15    [15428242]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить