Блог

Я хотел бы предоставить не-администратору возможность создавать папки на локальном жестком диске SQL server, используя xp_create_subdir. Причина — необходимо создать структуру папок, чтобы производственное оборудование могло передавать по FTP большие файлы. Метаданные для файлов хранятся в SQL.

Сервер — SQL 2016 Express. Операционная система — Windows 10 Pro.

Я нашел множество объяснений того, как заставить это работать, но не могу понять, чего мне не хватает. Используя учетную запись SA, я создал хранимую процедуру, подобную этой:

 use [DBname]
CREATE PROCEDURE dbo.usp_CreateDirectory
    @directoryFullPath varchar(500)
WITH EXECUTE AS owner
AS
BEGIN
    SET NOCOUNT ON;
    EXEC master.dbo.xp_create_subdir @directoryFullPath;
END
GO
GRANT EXECUTE ON dbo.usp_CreateDirectory TO [TestUser]
GO
  

Код для запуска хранимой процедуры:

 DECLARE @RC int
DECLARE @directoryFullPath varchar(500)
set @directoryFullPath = 'd:FTP_Root2020822'
EXECUTE @RC = dbo.usp_CreateDirectory
   @directoryFullPath
GO
  

В Windows я предоставил NT Service MSSQL $ {InstanceName} полный доступ к d:FTP_Root

Чего я не понимаю? Запуск xp_create_subdir ‘C:FTP_Root2020822 ‘ в MSSM работает нормально.

Запуск хранимой процедуры от имени SA или не-администратора TestUser дает этот результат:

Сообщение 229, уровень 14, состояние 5, Процедура xp_create_subdir, строка 1 [Строка запуска пакета 2] Разрешение на выполнение было отклонено для объекта xp_create_subdir, базы данных mssqlsystemresource, схемы sys.

Я нашел это на другом сайте:https://www.sqlservercentral.com/forums/topic/xp_create_subdir-for-non-sysadmins

Заголовки здесь состоят из двух основных моментов

1. Хотя это сообщение старое, чтобы решить эту проблему, вы должны убедиться, что вашей базе данных можно доверять, поскольку SP xp_create_subdir находится в другой базе данных

   Вам все еще нужно установить «с Execute как ‘dbo’

   изменить базу данных [DBNAME], установить значение trustworthy на

   — Парень-456224

2. И поймите последствия для безопасности использования SET TRUSTWORTHY ON. Возможно, это не проблема или… это может. «Это зависит», но вы не узнаете, пока не прочитаете об этом.

   — Джефф Моден

Я полностью согласен с Джеффом в этом вопросе. Если вы удаленно заботитесь о безопасности, поймите, что делает параметр TRUSTWORTHY, прежде чем настраивать его.

Я думаю, что главный вопрос здесь заключается в том, чтобы спросить, зачем SQL Server нужно создавать каталог? Powershell может как запрашивать у базы данных путь к каталогу, так и создавать каталог. У вас может быть задание агента SQL Server, которое будет выполнять это в контексте безопасности либо учетной записи прокси-сервера SQL Server, либо учетной записи службы SQL Agent (я бы выбрал учетную запись прокси-сервера лично, но это касается только меня).