Running SCHTASKS From A Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Running SCHTASKS From A Stored Procedure

Author

Topic

trafine
Starting Member

4 Posts

Posted - 2010-11-21 : 00:47:11

Hi,

I am trying to run a windows scheduled task from a stored procedure using xp_cmdshell and I keep getting the error Access is denied.

I can run the scheduled task from the task scheduler and from a windows command prompt without any errors.

I know the syntax is correct because I copied it from the stored procedure to the command prompt and it ran fine and the xp_cmdshell works fine because I am using it to run an SSIS package in the code directly below the process that is failing.

The OS is Windows Server 2008 and I am using SQL Server 2008. I am setup as a system administrator so I shouldn't have any permission problems.

Does anyone know what the problem may be.

Here is my code

DECLARE @strXPExecution varchar(100)
,@strArgusWindowsScheduledJob varchar(100)
,@strExecuteSSISPackage varchar(100)
,@strSSISPackage varchar(100)
,@intXPExecutionResult int

/* Execute the windows scheduled job */
SET @strXPExecution = 'SCHTASKS /RUN /TN OpenNotepad'
EXEC @intXPExecutionResult = master..xp_cmdshell @strXPExecution

-- EXECUTE the SSIS Package
SET @strSSISPackage = '\MSDB\PA Intranet\PKG Import Timberline Files'
SET @strExecuteSSISPackage = 'dtexec /DTS "' + @strSSISPackage +'" /SERVER IPSAPP07'
EXEC master..xp_cmdshell @strExecuteSSISPackage

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-21 : 10:56:01

the sql service account doesn't have permission.

xp_cmdshell is always run under the context of the service account

trafine
Starting Member

4 Posts

Posted - 2010-11-21 : 12:06:49

Hi Russell,

Thanks for your response. I checked to see what user SQL Server was running under using 'Select System_User' and it returned my user id. I am setup as an administrator in SQL Server. I am also an administrator in Windows Server 2008. Is some other permissions that need to be set to run this task?

Thank You

trafine
Starting Member

4 Posts

Posted - 2010-11-21 : 12:59:45

The service account the server was running under was Network Service. I changed it to Local System and my scheduled task ran fine.

Thanks for your help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-21 : 22:52:38

Glad to help.