BULK Insert security issues - 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

Import/Export (DTS) and Replication (2000)

BULK Insert security issues

Author

Topic

badman3k
Starting Member

15 Posts

Posted - 2009-05-22 : 10:35:13

I've been struggling with a problem with one of my Stored Procedures:

create table #tmp_mobile_usage (date varchar(20), time varchar(20), called varchar(30), mobile_id varchar(20), country char (3), key_word varchar(20), description varchar(100), usage varchar(15), cost varchar(20))

BULK
insert #tmp_mobile_usage FROM 'C:\wamp\www\csv\latest.csv' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')
set @bulk_err = @@error

I'm running this as a user, which has permission to run the stored procedures.

I get the error message: "The current user is not the database or object owner of table '#tmp_mobile_usage'"

I've done some serious searching of forums and websites and I'm struggling to find a definite solution to this problem.

Any help would be greatly appreciated.

Thanks in advance,
Rich

ashok_paswan
Starting Member

1 Post

Posted - 2009-05-27 : 12:30:33

Check owner of table '#tmp_mobile_usage please.

badman3k
Starting Member

15 Posts

Posted - 2009-05-28 : 06:54:30

quote:
Originally posted by ashok_paswan

Check owner of table '#tmp_mobile_usage please.

I'm not entirely sure how I would go about doing that, as it's a temporary table and is only created when the script is called. I'm assuming that it's the owner of the stored procedure.

badman3k
Starting Member

15 Posts

Posted - 2009-05-28 : 11:04:28

Okay I think I've come up with a solution, although I don't know how secure it is.

You need to put the user into the bulkadmin group. Then create a static table. Make the tables owner the user (using EXEC sp_changeobjectowner 'object', 'user') and then use the static table rather than the temporary table.

I modified my code so that it truncates the table, rather than dropping it - makes life easier that way.

Once the above is done, you shouldn't have any issues doing the BULK insert within the stored procedure, as the user owns the table.

Hope it helps someone else as I've looked high and low for this answer and only just stumbled across it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources