laneys.info Beware of Yankees, drunks, and loose women.

SQL Server 2000 has some really cool functionality.  Performing a BULK INSERT operation into a temporary table isn't one of them.

One of the application teams came to me a few months ago with a problem.  They were trying to do a BULK INSERT of some data into a temporary table, normalize the data in the temp table, then move the data out of the temp table into the permanent table.  However, they were getting an odd error when trying to do the BULK INSERT.

Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '<table_name>'. Cannot perform SET operation.

A quick Google search turned up Microsoft article 302621, "BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges," describing the exact problem they were having.  I implemented the workaround described in the Microsoft article, tested the fix to make sure it worked, and then closed the incident.

Then, about a month ago, the workaround stopped working.

I was dumbfounded.  Everything was still set up the way it had been, there hadn't been any changes to the server, and the application team insisted that they hadn't changed their code.  I got with my friend Vince, who contacted a DBA that we knew in another department.  She had some choice words to say about why doing a BULK INSERT into a temp table was "stupid" and that we should have the application team use a regular, user table instead.

Okay, now I must say that I'm certainly not a professional DBA like she is, but I've been doing database design and administration for about eight years now.  I got the basics of normalized database design while I was getting my Information Systems degree, I've done some advanced database design, and I've designed, built, and maintained some truly large databases in my day.  But I've never encountered this idea before about how using temporary tables is somehow bad, and that I should be taking the effort to manage my own tables and keep them clean instead of having the RDBMS do it for me.

I Googled around for some third-party explanation and all I found was a somewhat generic sentiment that the use of temporary tables was a "strong indicator" of bad design, that the programmer didn't understand how to do proper set-based processing and was using a temporary table in order to do procedural processing.  Bullocks.  That's just elitism and geeky oneupmanship, and it didn't help me solve the problem, though I could tell that to the application team and make them spend their time crafting a solution.  But then again, I just hate it when the stupid machine gets the better of me...

I decided to do it the old fashioned way: hacking.  I sat down with some test code and a disposable SQL Server instance that we had been using for some Disaster Recovery testing, and proceeded to fiddle around with the different database roles, server roles, and user permission settings.

In the end, I found that Microsoft's article only told half the story.  Microsoft says in the article that the problem appears when the user performing the BULK INSERT operation is a member of the bulkadmin server role, but not a member of the sysadmin server role or the database's db_ddladmin or db_owner roles.  The workaround they mention in the article is to add the user to one of those roles.  The fix I found was that the user needs to be a member of either the sysadmin server role or the db_ddladmin / db_owner role for the tempdb database, since that is where the temporary table is created.  Once I added the user to the db_ddladmin role in tempdb, the error message went away and I was BULK INSERTing like there was no tomorrow.

But there was still one problem: the tempdb database is dropped and recreated from scratch every time SQL Server starts.  How would I get the correct user permissions set up again without having to do it manually?  Well, Books Online came to the rescue.  I found where the sp_procoption stored procedure can be used to flag one or more stored procedures in the master database as being "startup" procedures that should be run when the server starts.  Cool.  So I type it in

CREATE PROCEDURE dbo.spAddTempdbUsers 
AS
	Use tempdb
	exec sp_grantdbaccess 'LoginAccount'
	exec sp_addrolemember 'db_ddladmin', 'LoginAccount'
GO

and got back

a USE database statement is not allowed in a procedure or trigger.

Crap.  Well, as much as I absolutely hate executing dynamic SQL, maybe that will work...

CREATE PROCEDURE dbo.spAddTempdbUsers 
AS
	Declare @sql varchar(100)
	Set @sql = 'Use tempdb; exec sp_grantdbaccess ''LoginAccount''; exec sp_addrolemember ''db_ddladmin'', ''LoginAccount'''
	Exec (@sql)
GO

Success!  So then,

EXECUTE sp_procoption 'dbo.spAddTempdbUsers', 'startup', 'true'

and stop and start the SQL Server instance.  I log back into the server and type

Use tempdb
exec sp_helpuser 'LoginAccount'

UserName         GroupName              LoginName
---------------- ---------------------- ----------------
LoginAccount     db_ddladmin            LoginAccount

Victory!  Having the hacker ethic does pay off sometimes...