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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment