User “Guest” Does Not Have Permission to Run DBCC CHECKIDENT For Object

DBCC CHECKIDENT Example

DBCC CHECKIDENT is used to check or change the value of an identity column in the specified table. To run this command, the login needs at least one of the following:

• own the schema that contains the table
• be a member of the sysadmin server role
• be a member of the db_owner or db_ddladmin database role

Here’s an example usage:

--Create test table
CREATE TABLE [dbo].[TestTable](
    [Id] int identity(1,1) NOT NULL,
    [Value] nvarchar(20) NULL
) ON [PRIMARY]
GO

--Insert test data
INSERT INTO [dbo].[TestTable]
VALUES('test1'),('test2'),('test3'),('test4'),('test5'),('test6'),('test7'),('test8'),('test9')

--check current identity value
DBCC CHECKIDENT (TestTable) 
--Checking identity information: current identity value '9', current column value '9'.

--Delete all records from the table
DELETE TABLE [TestTable]

--check current identity value
DBCC CHECKIDENT (TestTable) 
--Checking identity information: current identity value '9', current column value 'NULL'.
--Identity value is still 9 after deleting the rows. On the next insert, it will continue to Id 10 if you dont reseed 

--resets identity value
DBCC CHECKIDENT (TestTable, RESEED, 0) WITH NO_INFOMSGS

See here for additional details on DBCC CHECKIDENT.

DBCC CHECKIDENT on a temp table

If you’re not a sysadmin and attempt to run DBCC CHECKIDENT on a temporary table, you may run into this error:

User ‘guest’ does not have permission to run DBCC CHECKIDENT for object

Why does the error mention the “guest” user? The temp table exists in the tempdb system database. If the caller doesn’t have an associated user in tempdb, it will run the command as the “guest” account by default, This account will have limited permissions in the database. So, to run DBCC CHECKIDENT on a temp table, the caller needs to either be a sysadmin, or have an associated user in tempdb with db_owner or db_ddladmin database role.

Now, you may think everything’s fine, but on the next server restart the error returns. Whenever SQL Server is restarted, tempdb gets recreated, therefore all users and permissions granted in tempdb are lost. When tempdb gets recreated it uses the model system database as a template. So, to ensure the login retains the necessary permissions, you need to grant them in both tempdb and the model database.

I hope you found this post helpful. Please feel free to leave any questions or thoughts in the comments.