xp_cmdshell - Access is Denied problem

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

Transact-SQL (2005)

xp_cmdshell - Access is Denied problem

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-25 : 10:12:48

Good afternoon all,

I am trying to run the following but I get "Access is Denied".

declare
	@SP					sysname,
	@Instance			sysname,
	@SQL				varchar(2000),
	@theDate datetime

-- Initialise variables

set @SP	= object_name(@@procid)
set @Instance =  @@servername
set @theDate  = getdate()

set @SQL = 'sqlcmd -E -S DEREK\PREPRODUCTION -h-1 -d  Staging  -Q " set nocount on select ''""'' + cast(PersonId as varchar) + ''-'' + 
cast(DatabaseId as varchar) + ''""'' from Staging.dbo.EmailRemovalTest where DTS > dateadd(dd, -7, getdate()) set nocount off " 
> C:\testoutput' + convert(varchar, @theDate, 112) + '.txt'

exec xp_cmdshell @SQL

There is a sproc on the same server that uses xp_cmdshell. When I run that sproc it works fine. I wonder, does having execute permission on the sproc grants me permission to use xp_cmdshell? Anyway, how do I enable this setting?

Thanks for your help ni advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-25 : 10:14:11

Go to surface area configuration:
Enable Xp_cmdshell.

But you need to have admin rights

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:14:44

Surface area configuration tool.
Do your user which the code if running under (context) have permissions to the file and network share?

E 12°55'05.25"
N 56°04'39.16"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-25 : 10:22:21

The config_value for xp_cmdshell when I run sp_configure is set to 1, I tried running sp_configure 'xp_cmdshell', '0' but it comes back with permission denied. Grrrr!

Perhaps I am being stupid but this really doesnt make sense to me. If the setting is switched off, how can the other proc run fine?! Is this a global settnig or use setting?! You guys are answering my questions really fast so much appreciated but anymore help would be smashing!

cheers,

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-25 : 10:56:18

BOL

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

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 04:38:23

Hi guys,

I've solved my problem! What a silly boy I've been!!!! Turns out I didn't have access to the C drive on the server where SQL Server is installed. When I tested my sqlcmd command on my local instance it obviously worked as I have read/write access to my local C drive. When I changed the server instance, I didn't change the location of the output file. Turns out the Access denied was referring to writing to the C drive on the server and nothing to do with xp_cmdshell!

Sorry for the confusion, but I've learnt a valuable lesson!

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 05:13:19

quote:
Originally posted by Peso

Surface area configuration tool.
Do your user which the code if running under (context) have permissions to the file and network share?

E 12°55'05.25"
N 56°04'39.16"

Sorry Peso, I totally overlooked your question yesterday! You were right to ask this. The answer would have been NO but this has been sorted now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources