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

sql-server-2005 stored-procedures permissions temp-tables
Brad Knowles picture Brad Knowles · Oct 9, 2008 · Viewed 11.4k times · Source

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

Tim C picture Tim C · Oct 13, 2008

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?

Read More