Friday, February 24, 2012

Dbcc Checkident

Dear All,

I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.

Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about 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.

Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.

However I get the following:

User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

All users get the same message. This is an ASP.NET web app.

Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.

A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident

No comments:

Post a Comment