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

Откуда: Москва
Сообщений: 162
В проекте на C# есть код инициализации SqlDependency :
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
 connection.Open();

  //Создать команду
  SqlCommand cmd = new SqlCommand();
  cmd.CommandText = "SELECT ... FROM ... WHERE ...";
  cmd.CommandTimeout = 4 * 60;
  cmd.Connection = connection;

  //Включить отслеживание зависимостей
  SqlDependency depend = new SqlDependency(cmd/*, null, this._timeout*/);
  depend.OnChange += new OnChangeEventHandler(Dependency_OnChange);

  //Выполнить запрос
  SqlDataReader rdr = cmd.ExecuteReader();
}


В строке соединения указан SQL пользователь с ограниченным набором прав доступа.

Во время выполнения приложения возникает ошибка:
System.Data.SqlClient.SqlException (0x80131904): Cannot find the user 'owner', because it does not exist or you do not have permission.


Как показало расследование проблема возникает при выполнении динамически генерируемого скрипта:
declare @p3 uniqueidentifier
set @p3='94BE88B5-DF71-EB11-82DA-708BCDBF664A'
exec sp_executesql N'
IF OBJECT_ID(''SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf'', ''SQ'') IS NULL 
BEGIN 
	CREATE QUEUE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] WITH ACTIVATION (PROCEDURE_NAME=[SqlQueryNotificationStoredProcedure-603d11f3-f4c0-4aab-9d10-c59f984f9ddf], MAX_QUEUE_READERS=1, EXECUTE AS OWNER); 
END; 
IF (SELECT COUNT(*) FROM sys.services WHERE NAME=''SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf'') = 0 
BEGIN 
	CREATE SERVICE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] ON QUEUE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); 
	IF (SELECT COUNT(*) FROM sys.database_principals WHERE name=''sql_dependency_subscriber'' AND type=''R'') <> 0 
	BEGIN 
		GRANT SEND ON SERVICE::[SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] TO sql_dependency_subscriber; 
	END;  
END; 
BEGIN DIALOG @dialog_handle FROM SERVICE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] TO SERVICE ''SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf''',
N'@dialog_handle uniqueidentifier output',@dialog_handle=@p3 output
select @p3


А именно в конструкции:
CREATE QUEUE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] WITH ACTIVATION (PROCEDURE_NAME=[SqlQueryNotificationStoredProcedure-603d11f3-f4c0-4aab-9d10-c59f984f9ddf], MAX_QUEUE_READERS=1, EXECUTE AS OWNER); 


Какое разрешение GRAND нужно выдать чтобы скрипт был бы успешно выполнен.
Разрешение
GRANT CREATE SERVICE   TO [SqlUser];

я уже выдал.
18 фев 21, 17:14    [22282989]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1688
RedBird,

у пользователя должно быть право ALTER на схему в которой создается очередь.
18 фев 21, 19:55    [22283075]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
RedBird
Member

Откуда: Москва
Сообщений: 162
felix_ff
RedBird,

у пользователя должно быть право ALTER на схему в которой создается очередь.


Добавил
GRANT ALTER ANY SCHEMA TO [SqlUser];

Ошибка та же:
Cannot find the user 'owner', because it does not exist or you do not have permission.
18 фев 21, 20:22    [22283087]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
RedBird
Member

Откуда: Москва
Сообщений: 162
felix_ff,

Поставил
GRANT ALTER ON SCHEMA::[dbo] TO [SqlUser];

Эффект тот же:
Cannot find the user 'owner', because it does not exist or you do not have permission.
18 фев 21, 20:31    [22283090]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8314
RedBird,

что-то Вы не то делаете, почему у нас на каждый чих будет создаваться очередь?
По-моему, ее надо предварительно создать руками. Поищите справку.
18 фев 21, 20:32    [22283091]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
RedBird
Member

Откуда: Москва
Сообщений: 162
Владислав Колосов
RedBird,

что-то Вы не то делаете, почему у нас на каждый чих будет создаваться очередь?
По-моему, ее надо предварительно создать руками. Поищите справку.


1. SqlDependency штатное средство используемое в C# для подписки на изменения в таблицах БД.

2. Каждый раз очередь не создается. Она создается приложением сразу после подключения к БД с уникальным именем.
Затем отдельный поток ждет изменений в таблице. Когда приложение завершается, то очередь уничтожается.

3. Поскольку имя очереди уникально, то это дает возможности запускать два и более экземпляров приложения, причем каждое из них может иметь свою подписку. Именно поэтому нельзя заранее создать одну статическую очередь...
18 фев 21, 20:59    [22283103]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1688
RedBird,

чет у вас ругается на некоего странного пользователя 'owner'.

зайдите в ssms под своим пользователем "SqlUser"
и вручную выполните команду:
CREATE QUEUE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf]; 
DROP QUEUE  [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf];


если пройдет то выполните
CREATE QUEUE [SqlQueryNotificationService-603d11f3-f4c0-4aab-9d10-c59f984f9ddf] WITH ACTIVATION (PROCEDURE_NAME=[SqlQueryNotificationStoredProcedure-603d11f3-f4c0-4aab-9d10-c59f984f9ddf], MAX_QUEUE_READERS=1, EXECUTE AS OWNER);



да, я еще забыл для инстуркции CREATE QUEUE помимо alter на схему еще есть право на создание самой очереди
grant CREATE QUEUE to [SqlUser]

но он вам ругается не так как должен когда нет права на создание очереди.

Сообщение было отредактировано: 18 фев 21, 21:35
18 фев 21, 21:34    [22283118]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1688
аааа все я торможу.

У Вас же там процедурка активации with execute as owner

в таком случае у вас пользователь создающий очередь должен обладать правом impersonate на владельца схемы в которой живет очередь

поскольку явно схему вы не указываете, предполагаю что у вас очередь создается в схеме dbo, соответственно это владелец базы данных
grant impersonate on user::dbo to [SqlUser]


но учтите что это "жирное право", по сути вы открываете ящик пандоры для такого пользователя.

я бы посоветовал на вашем месте задуматься о процедуре "прослойке" в которую будут передаваться необходимые параметры, а разрешения будут наследоваться.

Сообщение было отредактировано: 18 фев 21, 21:48
18 фев 21, 21:53    [22283127]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
RedBird
Member

Откуда: Москва
Сообщений: 162
felix_ff,

ОГОРОМНОЕ СПАСИБО!!!
18 фев 21, 22:23    [22283139]     Ответить | Цитировать Сообщить модератору
 Re: Права в БД для SQL пользователя, использующего SqlDependency в проекте C#  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8314
RedBird,

если для [SqlUser] не указана схема по умолчанию (dbo), то он будет пытаться создавать объекты в схеме, совпадающей с его именем. Может быть проблема в этом.
19 фев 21, 01:32    [22283214]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить