Using Bulk Insert without administrator privileges

[dafdba]

We have an application running on SQL Server 2000 where all users occasionally have to run a stored procedure which does a bulk insert from a text-file. According to Microsoft article Q302621(

http://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=302621)

you need a lot of privileges to do this, which we don’t want to give to the application users. One of the options is to create tables owned by the user which has to do the bulk insert and give that user the bulkadmin role. This isn’t a problem when you only have 1 user who needs to do the bulk insert, but all application users must have this possibility. Another option is to use bcp, but then you need the extended stored procedure xp_cmdshell to start bcp from a stored procedure, and we don’t want to give users permissions on xp_cmdshell.

Does anybody know how to solve this without giving users to much privileges (bulkadmin is no problem but sysadmin, db_ddladmin, or db_owner are too much) ??? Is there a possibility to insert data from a text-file with using bulk insert ???

Thanks,
Harold

 

[rdroske]

Sounds like you need to defne an Application Role.

See Books Online for that topic.