Bulk Insert Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk Insert Permissions

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-03-06 : 11:42:36

I'm trying to write a stored procedure that does the BULK insert for an end user that runs a macro from an access database that calls this stored procedure (this works fine) ...

I'm getting an error saying the current user does not own the object that is being used...


CREATE PROCEDURE UpdateFullDirectory AS
	SET NOCOUNT ON

	CREATE TABLE #CDR (LastName VARCHAR(30), Firstname VARCHAR(30), Extension VARCHAR(30), Employee VARCHAR(30), PrintDial VARCHAR(30), DirectDial VARCHAR(30))
	CREATE TABLE #TELEDIR (Employee VARCHAR(30), Last VARCHAR(30), First VARCHAR(30), Nickname VARCHAR(30), ProcessLevel VARCHAR(30), Department VARCHAR(30), Description VARCHAR(30), Miscellaneous VARCHAR(30))

	BULK INSERT #CDR FROM '\\crp610\phonedirectory\cdr.txt' WITH
	(
		FIELDTERMINATOR='\t',
		FIRSTROW=2
	);
	
	BULK INSERT #TELEDIR FROM '\\crp610\phonedirectory\teledir.txt' WITH
	(
		FIELDTERMINATOR=',',
		FIRSTROW=1
	);

help?

I'd like to use temp tables if I can ... I would suffice with real tables if need be, but I'd prefer the temp tables ... when I execute this ... I have tried it with normal tables and it still gives me this error ...

Server: Msg 8104, Level 16, State 2, Procedure UpdateFullDirectory, Line 10 The current user is not the database or object owner of table '#CDR'. Cannot perform SET operation.

It gives the same error if the table was named 'CDR' and in the current database ... I don't want to grant this user any rights other than executing this stored procedure ... can this be done?

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-06 : 11:50:40

Yup, had the same problem. I had to change to bcp so users could load data. Didn't want to grant these levels of auth to a connection pooling id.

BOL:

Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.

Brett

8-)

PS:

I used:

SET @cmd = 'bcp ' + @db_name + '..Mthly_Ldgr_Dtl_Upld_Temp_1 in ' + @FilePathAndName + ' -t"~" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Select @Command_string

Exec(@Command_String)

Hope this helps.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-03-06 : 12:49:49

ya ... i guess i'll just give the user bulk insert permissions ... trying to stay away from xp_cmdshell as i don't want end users to be able to login and run that procedure ...

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-03-06 : 13:02:35

I added the user to the bulkadmin role and it tells me now:

You do not have permission to use the BULK INSERT statement

... do i have to recycle the server or something?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-06 : 13:16:17

You definitely should not have to reboot the server to get this permission to work. Try running this stored procedure from Query Analyzer while logged in as the user. See if you get the error there. If you don't, then however the user calls this stored procedure is passing the wrong credentials.

Tara

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-03-06 : 13:17:39

i logged in as the SQL user with query analyzer and that's what gave me the error message ... maybe I should try logging out :) and back in ...

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-06 : 13:21:33

I know you'd have to use a new connection. How are you accessing this? Though QA?

Also my xp_cmdsheel is through a sproc. I don't let any dynamic sql against our dbs. And we use conncection pooling with 1 user id, and handle security within the application itself.

Brett

8-)

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-03-06 : 13:30:51

whenever i tried to execute xp_cmdshell with the user logged in it would complain about permissions ... even if the user was calling a stored procedure that called xp_cmdshell

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources