xp_cmdshell security

[edmundo]

Hi,

one of the outputs of a stored procedure I have written creates a text file. I have achieved this by using xp_cmdshell in conjuntion with a bcp command. My question surrounds security.

Because of the mixed environment we have in our company I have found it easier for the time being to use SQL authentication. Unless I am mistaken (and I hope I am) you cannot run xp_cmdshell without being a member of the sysadmin group. This is quite a powerful group to be a member of and not something I am keen to add a user to. I do understand that xp_cmdshell is a powerful command, though I cannot think of a better way to create the text file..

If anyone could enlighten me as to how to run this command or indeed create a text file without putting a security risk on the database, I'd be a very happy man...

cheers in advance

Ed

 

[rajeevnandanmishra]

Hi Ed,
Whatever routine you can run to generate text file, you have to use xp_cmdshell.
Your concern about the security is quite OK. But you can grant right on xp_cmdshell to all your users/ or (group).