I have the following stored procedure in SQL Server 2000:
CREATE PROCEDURE [dbo].[sp_CSV_COPROD] AS
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp partman.dbo.vw_export_coprod out \\SQLSVR1\c$\Co-Products.csv -S SQLSVR1 -T -c -t,'
EXEC master..xp_cmdshell @bcpCommand
I can run this fine and it outputs the required csv file (this is because I am an admin). However, when a non admin user runs this sp they get an error about permissions for xp_cmshell. I am using windows authentication.
Can someone please explain the best way to go about allowing non-admin users permissions to running this? I don't particularly want to give my Domain Users windows group execute permissions on the master db.
I am a COMPLETE NOVICE at this so hand holding may be required!