Microsoft KB Archive/243023

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

Article ID: 243023

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q243023



SYMPTOMS
In SQL Server 7.0 or later, 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.   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'  Run sp_procoption to mark the procedure startup option to true.

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

Keywords: kbbug kbprb KB243023

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.