How to let user use xp_cmdshell?

Hi
I wonder to know Hot to let SQL Server user (not Admin role) use
xp_cmdshell?
Cause the execution response access denied.
What should I do? Thanks!
AngiOnly members of the 'Admin' role can execute xp_cmdshell. And there's really
not any options.
What are you attempting to accomplish?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>|||Thanks!
It's too bad that only Admin role can execute it.
I write a store procedure and use xp_cmdshell to execute .exe file on
localhost.
Casue the user include non-Admin role, so I want to know is any way that
user can execute xp_cmdshell.
Any good idea? Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||Oh, I'm use SQL Server 2005!
Any way can let user execute " xp_cmdshell" ?
Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||On Thu, 13 Jul 2006 10:06:15 +0800, "angi" <angi@.news.microsoft.com>
wrote:

>Thanks!
>It's too bad that only Admin role can execute it.
>I write a store procedure and use xp_cmdshell to execute .exe file on
>localhost.
>Casue the user include non-Admin role, so I want to know is any way that
>user can execute xp_cmdshell.
>Any good idea? Thanks!
>Angi
I worked around this on SQL Server 7.0, not sure I ever tried it on
2000. I can't say that I played with it any time recently.
I created a proc in master. That proc executed xp_cmdshell. I then
granted exec on that proc to public.
create proc dbo.TestThis
as
exec master..xp_cmdshell 'dir'
GO
GRANT EXECUTE ON [dbo].[TestThis] TO [public]
GO
Of course in production I would rather not grant anything to public.
Roy Harvey
Beacon Falls, CT|||Perhaps if you gave us more information about what you are attempting to
accomplish we might be of better help.
Personally, I can't imagine why you would want to do something so
'dangerous' and in contravention to 'Best Practices'. More information might
help us understand. (Remember, if a user and execute xp_cmdshell, the user
could do the following:
EXECUTE xp_cmdshell "net stop MSSQLServer"
EXECUTE xp_cmdshell "Format C:"
Why would you ever give anyone other than an administrator that much power
over your server?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:%23bPijJipGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Oh, I'm use SQL Server 2005!
> Any way can let user execute " xp_cmdshell" ?
> Thanks!
> Angi
> "Arnie Rowland" <arnie@.1568.com>
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Roy and Arnie so concern about this issue.
Why am I attempting to accomplish this dangerous issue?
Because I want to show a image on Report Server and Where is the image
source?
The image source comes from store procedure to execute a .exe file and the
.exe file gets some kinds of user parameter to produce their own image.
After produce the image file then bluk insert the image file as binary data
type into database.
Last, user from Report Server to see their own image information.
During these processes, the most critical is use xp_cmdshell to execute the
.exe file.
I post procedure code as fellow, hope will help.
And thanks for help me 'Discovery' the Best Practices! ^^
Appreciate!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a
4-0ce39c30ec38.htm --ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f566db3
e-0a3b-4a61-9c84-49f8d42f5760.htm SET @.Header = '1 SQLIMAGE 0
' SET @.Tailer = ' ""
2 data ""' --replace FileSize format SELECT @.FileSize = REPLACE((
SELECT SUBSTRING(FName,30,10) AS FileSize FROM #DirList),',','') --rebuild b
cp.fmt Format SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath EXEC Master..XP
_CMDS
HELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath EXE
C Master..XP_CMDSHELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo ' + @.Hea
der + @.FileSize + @.Tailer + ' >> ' +@.BcpFilePath EXEC Master..XP_CMDSHELL @.F
ullText , NO_OUTPUT --Files
is a physical table used to save Binary information TRUNCATE TABLE dbo.Files
--image INSERT to Files SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.
GaugeFilePath +@.GaugeFileName + '''' + ' WITH ( FORMATFILE = ''' + @.BcpF
ilePath + ''' ) ' EXEC SP_
EXECUTESQL @.FullText --delete physical image SET @.DelCmdString = 'del ' + @.
GaugeFilePath + @.GaugeFileName EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT --
clear all the files SELECT * FROM Files TRUNCATE TABLE Files DROP TABLE #Dir
ListGO"Arnie Rowland" <arni
e@.1568.com> glsD:%23m$1c4jpGHA.2464@.TK2MSFTNGP03.phx.gbl...> Perha
ps if you gave us more information about what you are attempting toaccomplis
h we might be of better help.>> Personally, I can't imagine why you would wa
nt to do something so'dange
rous' and in contravention to 'Best Practices'. More information mighthelp u
s understand. (Remember, if a user and execute xp_cmdshell, the usercould do
the following:>> EXECUTE xp_cmdshell "net stop MSSQLServer"> EXECUTE xp
_cmdshell "Format C:">> Why
would you ever give anyone other than an administrator that much powerover y
our server?>> --> Arnie Rowland*> "To be successful, your heart must accompa
ny your knowledge.">>>> "angi" <angi@.news.microsoft.com> wrote in messagenew
s:%23bPijJipGHA.4268@.TK2MS
FTNGP04.phx.gbl...>> Oh, I'm use SQL Server 2005!>> Any way can let user exe
cute " xp_cmdshell" ?>> Thanks!>>>> Angi>>>> "Arnie Rowland" <arnie@.1568.com
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...>>> Only members of
the 'Admin' role can execu
te xp_cmdshell. And there'sreally not any options.>>>>>> What are you attemp
ting to accomplish?>>>>>> -->>> Arnie Rowland*>>> "To be successful, your he
art must accompany your knowledge.">>>>>>>>>>>> "angi" <angi@.news.microsoft.
com> wrote in messagenews:u
NhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...>>>> Hi>>>>>>>> I wonder to know Hot
to let SQL Server user (not Admin role) usexp_cmdshell?>>>> Cause the execut
ion response access denied.>>>> What should I do? Thanks!>>>>>>>> Angi>>>>>>
>>>>>>>>>>|||I repost the SP Code
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
SET @.Header = '1 SQLIMAGE 0 '
SET @.Tailer = ' "" 2 data ""'
--replace FileSize format
SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
FROM #DirList),',','')
--rebuild bcp.fmt Format
SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
@.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
--Files is a physical table used to save Binary information
TRUNCATE TABLE dbo.Files
--image INSERT to Files
SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
@.GaugeFileName + '''' +
' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
EXEC SP_EXECUTESQL @.FullText
--delete physical image
SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
--clear all the files
SELECT * FROM Files
TRUNCATE TABLE Files
DROP TABLE #DirList
GO|||The xp_cmdshell page in BOL
(http://msdn2.microsoft.com/en-us/library/ms175046.aspx) indicates that you
need CONTROL SERVER permissions to execute xp_cmdshell.
SQL Server Reporting Services may be the wrong tool to use to create custom
interactive images to display to the user. Most likely, that would be better
handled in a different client application.
If you have boxed yourself in and have no choice but to use xp_cmdshell,
then you will have to provide the users admin privileges over the entire
server -and I think that would be a major security issue and mistake.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"angi" <angi@.news.microsoft.com> wrote in message
news:OOjhca7rGHA.2240@.TK2MSFTNGP04.phx.gbl...
>I repost the SP Code
>
> CREATE PROCEDURE dbo.spImportGauge
> (
> @.GaugeType NVARCHAR(10),
> @.Orgn NVARCHAR(20),
> @.KpiParent NVARCHAR(10),
> @.KpiUserMode NVARCHAR (5),
> @.GaugeApPath NVARCHAR(200), --Gauge AP's location
> @.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and
> same as fmt\bcp.fmt's location
> )
> AS
> DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
> DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
> DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
> DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
> DECLARE @.FileSize NVARCHAR( 20) --save image file size
> DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
> DECLARE @.Header NVARCHAR(200) --Bcp format's Header
> DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
> DECLARE @.FullText NVARCHAR(500)
> DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
> folder string
>
> -- generate GUID as a unique image file name
> SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
> SET @.GaugeFileName = @.GaugeGUID + '.jpg'
> --Import Image bcp.fmt file location
> SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
> --Check Gauge type
> IF @.GaugeType = 'Gauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear2'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'miniGauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
>
> --use XP_CMDSHELL to execute AP
> EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
> --use temp table to store @.ExeCmdString result
> SET DATEFORMAT MDY
> IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #DirList (FName NVARCHAR(2000))
> --setting Import file's path and delete non-image information
> SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
> INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
> DELETE #DirList WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> --Import picture's FormatAreference Online Book's bcp
> SET @.Header = '1 SQLIMAGE 0 '
> SET @.Tailer = ' "" 2 data ""'
> --replace FileSize format
> SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
> FROM #DirList),',','')
> --rebuild bcp.fmt Format
> SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
> @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> --Files is a physical table used to save Binary information
> TRUNCATE TABLE dbo.Files
> --image INSERT to Files
> SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
> @.GaugeFileName + '''' +
> ' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
> EXEC SP_EXECUTESQL @.FullText
> --delete physical image
> SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
> EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
> --clear all the files
> SELECT * FROM Files
> TRUNCATE TABLE Files
> DROP TABLE #DirList
> GO
>|||Have you explored the following (source is BooksOnline):
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in whic
h
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
"angi" wrote:

> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>
>