Чтобы запускать хранимую
процедуру xp_cmdshell,
нужно иметь разрешения CONTROL SERVER на сервер, т.е быть в роли sysadmins. Пользователям MS SQL Server-а, которые не входят в
данную роль, предоставить права на
выполнение данной процедуры будет мало
только дать права на выполнение данной процедуры, но и нужно создать учетную
запись-посредник для процедуры xp_cmdshell.
Для начала предоставим учетной записи права на выполнение xp_cmdshell:
use [master]
GO
GRANT EXECUTE ON [sys].[xp_cmdshell] TO [test_user]
GO
Без них будет ошибка :
Сообщение 229, уровень 14,
состояние 5, процедура xp_cmdshell, строка 1
Запрещено разрешение
"EXECUTE" на объект "xp_cmdshell" базы данных
"mssqlsystemresource", схемы "sys".
Затем создадим учетную запись посредник. Создается она двумя
способами:
1)
Через процедуру sp_xp_cmdshell_proxy_account, пример
EXEC sp_xp_cmdshell_proxy_account 'user_name','Password'
Где user_name должно быть либо Windows учетная запись сервера, либо
учетная запись домена.
2)
Через консоль Microsoft Management Studio
Свойство
сервера ->
Безопастность
Поставить галочку «Включить серверную учетную запись - посредник»
Прописать
учетную запись и пароль.
Все, после этого наша учетная запись может запускать
процедуру xp_cmdshell.
Несколько
слов по выбору учетной записи посредника – выбирайте её исходя из требуемых
прав и разрешений в сети и на сервере.
Кстати,
сначала надо разрешить вообще запускать процедуру xp_cmdshell, командой
exec sp_configure 'xp_cmdshell',1
go
reconfigure
по умолчанию данный параметр вылючен.
Кстати, написание данного поста меня натолкнул админ 1с, когда я ему переносил БД 1с и он поросил права sysadmin-а, т.к в коде использовал процедуру xp_cmdshell, пришлость права порезать и предоставить только необходимые.
Вот так, нечего иметь слишком большие права.
Константин, с огромным удовольствием читаю сейчас Ваш блог, и все скрипты плавно перетаскиваю в свою копилку. Просто жемчужины - четко, по делу, все понятно.
ОтветитьУдалитьОбратила внимание, что часто посты без комментариев, и подумала, что, наверное, так немного обидно, вроде как нет ответной реакции (читают? не читают?) - вот потому и пишу - читают, и еще как читают!!! Громадное Вам спасибо за ваш труд!!! Уверена, что очень многие скажут Вам то же самое.
Конкретно по этому посту - класс, очень актуально!
С приветом из Иерусалима :)))
Здравствуйте. Спасибо за слова. Только я не Константин, кстати откуда такое имя взяли?
ОтветитьУдалитьС приветом из России! Если нужна помощь или желаемые статьи по MS SQL Server пишите.
Ага спасибо!
ОтветитьУдалитьпожалуйста
УдалитьДень добрый! Столкнулся с проблемой при использовании xp_cmdshell при копировании резервной копии базы данных на NAS который не введен в домен.
ОтветитьУдалитьСкрипт T-SQL отрабатывает, в журнале пишет что все замечательно, но необходимые действия не производятся. Пользователь в "Свойства сервера -> Безопастность" заведен. Текст скрипта следующий:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- Connecting NAS Drive
EXEC xp_cmdshell 'net use b: \\192.168.19.240\Base_BACKUP backup /user:backup /persistent:no'
GO
Причем перед выполнением backup'a файл спокойно удаляю командой:
EXEC xp_cmdshell 'del /f /q D:\BACKUP\DB_Backup.dbk, NO_OUTPUT';
GO
В чем может быть ошибка?
Попробовал сделать через запрос следующий скрипт
ОтветитьУдалитьEXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'net use b: \\192.168.19.240\Base_BACKUP backup /user:backup /persistent:no';
GO
В результате получаю:
Системная ошибка 85.
NULL
Имя локального устройства уже используется.
NULL
NULL
Вопрос снимаю, т.к. в качестве учетной записи в "Свойства сервера -> Безопастность" используется локальный, а не доменный пользователь под которым логинятся в активную сессию сервера, возможно из-за этого и происходит ошибка с выполнением сетевых действий.
ОтветитьУдалитьОставил текущую схему работы резервирования:
1. Удаляется старый файл резервной копии (места на диске немного и возможна ситуация, когда при попытке созадть резерную копию места под нее не будет, удаляя предварительно файл, мы решаем эту проблему)
2. Создаем резервную копию:
backup database dvdb to DB_Backup.dbk with init
3. Средствами планировщика Windows запускаем скрипт на копирование созданной резервной копии на NAS.
Если физически дисик разные, то это даже плюс, т.к нахождение рядом копии на независимом дике это даже лучше и бытрее восстанавливать .
УдалитьСкорее всего предыдущие операции не проходили из-за прав, как вариант временно давать все полные права и уже потом их урезать, до нужных.
Физические диски разные, копия создается на диске локальном, а потом должна копироваться на NAS. Сейчас выполнятеся скриптом через планировщик Windows, но захотел это сделать через задачи агента MS SQL.В результате не хватает прав для присоединения сетевого диска, ну и соответственно и копирования. При выполении запроса "exec xp_cmdshell 'whoami'" получаю "nt service\mssqlserver", хотя насколько я понимаю должен получать информацию о том пользователе что указан через консоль Microsoft Management Studio.
ОтветитьУдалитьДобрый день. Подскажите, а как сделать то же самое в хранимой процедуре SQL Server 2008? Использовать команду "use" в теле процедуры запрещено. А до команды "ALTER PROCEDURE" (CREATE PROCEDURE) внести и сохранить изменения не получается.
ОтветитьУдалитьСпасибо.
Фигня, "Использовать команду "use" в теле процедуры" можно, нужно только закатать ее в строку и подставить в sp_executesql
Удалить