xp_cmdshell bcp utility other than sa account

[dyan24]

I have a stored proc that has a line:

exec master..xp_cmdshell 'bcp "SELECT * FROM ##temp_ac540"'

but since i am not an sa and there's a company policy for sa account, is there another way that i can run this from not being an sa.

Please help!!

Thanks in advance.

 

[Jamfool]

Info on what is required to run xp_cmdshell:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

Perhaps bcp/xp_cmdshell could be changed for dts?

 

[dyan24]

Thanks Jamfool!

Yes, our DBA says we can use a proxy but we can run the job thru scheduling only.

Is there a way to run this stored proc as a proxy by running my xp_cmdshell above?

 

[Jamfool]

if the xp_sqlagent_proxy_account is configured then the account can run xp_cmdshell. Just get the DBA to configure an account for you with the relavent permissions, then when you run the proc everything will be fine.

 

[dyan24]

so, does it mean that the proxy account is not limited only to scheduling jobs?

Can you please confirm that and do you have a link on the msdn that i can show to our DBA?

Thanks again!

 

[Jamfool]

xp_sqlagent_proxy_account

Sets or retrieves the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. The proxy account is a Microsoft® Windows® account in whose security context the jobs or command prompt commands are run.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

 

[mrdenny]

They sql agent proxy account is used by the SQL Agent only. It's not available in non-agent jobs.

Use of xp_cmdshell is a security nightmare and it's use shouldn't be used. Instead of impedding xp_cmdshell within your stored procedure have the stored procedure return a record set.

Then when you need to dump to data to a file you can run bcp directly from the command line and using the QUERYOUT to dump the data from the stored procedure to a text file.

bcp "Database.dbo.sp_ProcName" queryout "c:\filename.txt" -w -b 1000 -S SQLServer -T

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com