PRB: Error 8104 Occurs with Non-Admin Owner Using Set IDENTITY_INSERT

• Microsoft SQL Server version 7.0

SYMPTOMS

In SQL Server 7.0, when a non-admin user (a user that is not a member of a db_owner role or a user that is not aliased to a database owner [DBO]) creates a temporary table with an identity column and tries to set IDENTITY_INSERT on, the following error 8104 occurs:

8104 The current user is not the database or object owner of table '%.*ls'. Cannot perform SET operation.

CAUSE

This problem occurs because the owner of temporary tables created by any user is DBO. However, whenever you attempt to process anything in tempdb your login is mapped to guest user.

WORKAROUND

To work around this problem, make the guest account in tempdb a member of the db_owner role. However, since tempdb is recreated at every startup, you must do this inside a startup procedure.

1. Create a stored procedure in the master database as an administrator user with the following in the stored procedure body:

   
   exec tempdb..sp_addrolemember 'db_owner','guest' 




2. Run sp_procoption to mark the procedure startup option to true.

Additional query words: IDENTITY_INSERT, TSQL, temporary tables, set

Keywords : kbbug7.00 kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbprb