如何从存储过程执行 PowerShell 文件 [英] How to execute a PowerShell file from stored procedure

问题描述

如何从存储过程中执行 PowerShell 文件?

How do I execute a PowerShell file from inside a stored procedure?

我在存储过程中执行 PowerShell 文件时没有任何运气.

I have not had any luck executing the PowerShell file from within a stored procedure.

exec xp_cmdshell 'powershell ""E:\PowershellScripts\todayErrorLog.ps1""'

当我运行存储过程时，我得到文件的访问被拒绝错误.我知道 ps1 文件本身很好，因为我可以在命令行和代理作业中执行它.所以我认为这是给存储过程足够的权限的问题.

When I run the stored procedure, I get access is denied errors on the file. I know the ps1 file itself is good because I can execute it both on the command line and as an Agent job. So I figure it's a matter of giving the stored procedure sufficient permissions.

以前，我确保使用此脚本启用 xp_cmdshell

Previously, I made sure to enable xp_cmdshell with this script

-- 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  

为了从存储过程执行 PowerShell 脚本文件，我还需要做什么?

What else do I need to do in order to execute the PowerShell script file from the stored procedure?

这是存储过程:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RefreshErrorQuery] 
    -- No parameters    
AS
BEGIN
    SET NOCOUNT ON;
    -- Step 1
    exec xp_cmdshell 'powershell ""E:\PowershellScripts\todayErrorLog.ps1""'

    -- Step 2: TODO: Run SSIS package
END

推荐答案

您的问题是因为代理使用的帐户没有足够的权限来访问该文件.

Your problem is because the account that the agent is using does not have sufficient permissions to access the file.

如何检查:

(1) 可以从命令行运行 PS(2) 您不能从 xp_cmdshell 中运行脚本(3) 将包含文件的文件夹更改为所有人完全控制(4) 尝试通过 xp_cmdshell 运行脚本

(1) You can run PS from the command line (2) You cannot run the script from within xp_cmdshell (3) Change the folder containing the file to full control by Everyone (4) Try running the script via xp_cmdshell

或者，对于测试，您可以将运行 SQL 代理的登录名更改为您的凭据.然后代理以你"的身份运行，就像你在命令行上运行一样.

Alternatively for a test, you can change the login SQL Agent is running under to your credentials. Then the agent is running as "you", the same as if you are running on the command line.

这篇关于如何从存储过程执行 PowerShell 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！