Results 1 to 5 of 5

Thread: Dbcc Checkident

  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2005
    Posts
    122
    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.

  3. #3
    Join Date
    Mar 2005
    Posts
    92
    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?

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    92
    Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •