DBCC CHECKIDENT on a temporary table throwing permissions error for wrong user

Question

I'm logged into a SQL Server 2005 database as a non-sa user, 'bhk', that is a member of the 'public' server role only. The following code tries to execute within a stored procedure called by user 'bhk'. This line of code...

TRUNCATE TABLE #Table1
DBCC CHECKIDENT('#Table1', RESEED, @SequenceNumber) WITH NO_INFOMSGS

causes this error...

User 'guest' does not have permission to run DBCC CHECKIDENT for object
'#Table1__00000000007F'.

I'm aware of the permissions required to run DBCC CHECKIDENT...
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

So I have two questions:

1. Since 'bhk' is calling a stored procedure that creates a temporary table, shouldn't 'bhk' be the owner and be allowed to run DBCC CHECKIDENT?
2. Why does the error message return that user 'guest' doesn't have permission? To my knowledge, I'm not logged in as 'guest'.

Any help would be greatly appreciated.

Answer 1

Here is an alternate solution, that may work if you need to re-seed with a sequence number of more than 1.

TRUNCATE #Table1

SET IDENTITY_INSERT #Table1 ON

INSERT INTO #Table1 (TableID) -- This is your primary key field
VALUES (@SequenceNumber - 1)

SET IDENTITY_INSERT #Table1 OFF

DELETE FROM #Table1

What this is doing is to set the IDENTITY_INSERT on your temporary table, to allow you to add a row with an explicit ID. You can then delete this row, but further inserts should start from the last sequence number.