leyton.org

One of the developers I support recently came across the following error message in Microsoft SQL Server 2000 (SP4):

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.

Unfortunately, there’s little out on the interwebs about this beyond references to a Microsoft Knowledge Base workaround (KB 302621), which concedes it’s a bug and you need to grant serious permissions to the user.

Unfortunately, we just didn’t have that luxury. With a complex permissions system and very sensitive data, we simply couldn’t let users have db_ddladmin or db_owner on this system. We’ve had inadvertent accidents where this had been erroneously granted, and we’d finally worked those out. We just didn’t want to resort to that.

Whilst alternatives to the BULK INSERT operation were possible, they weren’t desirable. And I’m afraid I’m the sort of DBA who likes to understand the specifics of an issue before accepting alternatives. In this issue, it just didn’t feel ‘right’ to grant such powerful roles. So the developer (let’s call him Andrew) and I worked through various theories. The BULK INSERT was working elsewhere, so why not on this specific table? We ruled out permissions and constraints, but finally came to realise it was because the table had an IDENTITY column. The proof was when BULK INSERT worked correctly, without the above error, when we didn’t use the KEEPIDENTITY option.

This makes sense, as the ’seed’ for the IDENTITY column is meta information, and stored elsewhere on the table structures. If the bulk insert is providing values for the column, the ’seed’ needs to be updated afterwards for any subsequent (normal) inserts that do not provide a value. Our presumption is that the permission model is simply over zealous in this case.

Sadly, Microsoft don’t seem to mention that losing IDENTITY values is another workaround. In our case, because we still needed unique values, we could switch to a number fountain (ie. select max(id)+1 from table with repeatable read) - The IDENTITY was a nice simplification. Others may not be so lucky. But figured it was high time I posted some technical details to ‘give something back’, given this was the very reason I setup this website in the first place.

This entry was posted on Thursday, January 24th, 2008 at 1:36 pm and is filed under Databases, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.