enabling xp_cmdshell in SQL Server 2005 Express

[jacktek]

I am able to enable xp_cmdshell in SQL Server 2005 Express by going to Start / All Programs / Microsoft SQL Server 2005 / Configuration Tools / SQL Server Surface Area Configuration.

However - I need to be able to enable / disable xp_cmdshell using scripts. We build service packs that go over the internet to many customers, and these service packs use bcp to load tables w/updated data.

After several circles on the MS online help merry-go-round I searched the internet. I found the below suggested solution:

exec sp_configure 'xp_cmdshell', '1'

When I run it I get:

"The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option."

I'm thinking there is probably a simple solution to my script dilemma.

 

[SQLDenis]

EXECUTE sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 
GO 

EXECUTE sp_configure 'xp_cmdshell', '1' 
RECONFIGURE WITH OVERRIDE 
GO 

EXECUTE sp_configure 'show advanced options', 0 
RECONFIGURE WITH OVERRIDE 
GO

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

New and improved means a bit more work. Check this out:

http://www.aspfaq.com/sql2005/show.asp?id=29

< M!ke >
I am not a hamster and life is not a wheel.

 

[LNBruno]

Or you could just copy, paste & run what Denis posted....

LOL!

< M!ke >
I am not a hamster and life is not a wheel.

 

[LNBruno]

For those of you who don't want to follow the link, here's the reasoning:

As you are getting up to speed with SQL Server 2005, you might notice that scripts using master.dbo.xp_cmdshell no longer work:

Msg 15501, Level 16, State 1, Procedure xp_cmdshell, Line 1 
This module has been marked OFF. Turn on 'xp_cmdshell' in order to be able to access the module.

This change is by design and is part of Microsoft's overall "secure by default" approach. However, the error message is not very helpful in solving the problem, as there is no magic switch in Management Studio to enable this feature. To turn xp_cmdshell functionality back on, use the sa account or another administrator, and issue this code:

USE master 
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO

If you try this by itself:

EXEC sp_configure 'xp_cmdshell', 1

You will get this error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 50 
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

well actually there is a magic switch

Surface Configuration Tool 
You have to navigate to the tool from the start button, the path is below 
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool 

Then select Surface Area Configuration for Features (bottom one) 
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

SWEET!

Star for the info, Denis!

< M!ke >
I am not a hamster and life is not a wheel.

 

[jacktek]

Thank you Denis, your script (SQL statements) are just what I needed.

Perfec-chi-onnn

I had already found the 'magic button'. I need to be able to automate the process, and your script provides me w/the building tools to accomplish that.

Thanks again

 

[SQLDenis]

np tfts

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions