Using BCP/xp_cmdshell as NON serveradmin

[Lauren Quantrell]

I have created a view named viewOutput that shows one column in a
table.
I insert a row into the table and then I'm using this code to create a
file with the text in the single row.

This code works fine when I'm signed on with an account that has
server admin rights, however it fails when I signin with an account
that does not have server admin rights.

I'm hoping someone can help me with this.
Alter PROCEDURE OutputOrders
@FileName nvarchar(50)

AS
set nocount on

DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myServerName.dbo.viewOutput out "c:\output\order files\' +
@TemplateFileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master.dbo.xp_cmdshell @ExportCommand
I have granted execute permission to xp_cmdshell for both users.

[Simon Hayes]

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

I have created a view named viewOutput that shows one column in a
table.
I insert a row into the table and then I'm using this code to create a
file with the text in the single row.

This code works fine when I'm signed on with an account that has
server admin rights, however it fails when I signin with an account
that does not have server admin rights.

I'm hoping someone can help me with this.
Alter PROCEDURE OutputOrders
@FileName nvarchar(50)

AS
set nocount on

DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myServerName.dbo.viewOutput out "c:\output\order files\' +
@TemplateFileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master.dbo.xp_cmdshell @ExportCommand
I have granted execute permission to xp_cmdshell for both users.

Unfortunately, you didn't explain what "fails" means - what is the
error? But I guess that probably you haven't enabled the SQL Agent
proxy account. When a sysadmin runs xp_cmdshell, the commands executes
in the security context of the MSSQL service account, however for
non-sysadmins you need to specify an account to use. See the Remarks
section for xp_cmdshell in Books Online.

Simon

[Lauren Quantrell]

Simon,
After posting I found an excellent article by Dan Guzman on this
subject at: http://www.winnetmag.com/Articles/Index.cfm (search for
article #25145)
Thanks
sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

I have created a view named viewOutput that shows one column in a
table.
I insert a row into the table and then I'm using this code to create a
file with the text in the single row.

This code works fine when I'm signed on with an account that has
server admin rights, however it fails when I signin with an account
that does not have server admin rights.

I'm hoping someone can help me with this.
Alter PROCEDURE OutputOrders
@FileName nvarchar(50)

AS
set nocount on

DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myServerName.dbo.viewOutput out "c:\output\order files\' +
@TemplateFileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master.dbo.xp_cmdshell @ExportCommand
I have granted execute permission to xp_cmdshell for both users.

Unfortunately, you didn't explain what "fails" means - what is the
error? But I guess that probably you haven't enabled the SQL Agent
proxy account. When a sysadmin runs xp_cmdshell, the commands executes
in the security context of the MSSQL service account, however for
non-sysadmins you need to specify an account to use. See the Remarks
section for xp_cmdshell in Books Online.

Simon