What permissions are necessary to issue "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

  • Jon Ryan

    Old Hand

    Points: 328

    When logged on as a particular user and attempting to run "ALTER TABLE <tblName> NOCHECK CONSTRAINT ALL", I am getting the message, "User does not have permission to perform this operation on table '<tblName>'."  I have tried playing with the server and database fixed roles, but no luck.  I then looked for object specific rights, but the closest I could find was "REFERENCES".  I have granted "REFERENCES" to the user, but still no luck.

    Any assistance is greatly appreciated.

    Jon

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    I believe you need to be the table owner, db_owner, or ddl_admin to alter a table.

  • Jon Ryan

    Old Hand

    Points: 328

    My user has been granted ddl_admin and db_owner within the database.  The table owner is dbo.  Still getting the same error msg.

    Thanks!

  • jayrocks20

    SSC Enthusiast

    Points: 146

    I have a selfreferenced foreignkey table . Deleting records from this table is complicated.

    I am using a CTE to fetch all child records to be deleted together to avoid foreign key constraint error however it has performance issues and locks the table for long.

    as an alterative I though of :

    ALTER TABLE

    NOCHECK CONSTRAINT FK_XXX

    DELETE FROM TABLE WHERE XXX

    ALTER TABLE CHECK CONSTRAINT FK_XXX

    I want to know the Risks/ drawbacks involved in using this ALTER TABLE NO CHECK CONSTRAINT on production servers.

    Thanks..

  • HanShi

    SSC-Dedicated

    Points: 33142

    jayrocks20 (10/2/2014)


    I have a selfreferenced foreignkey table . Deleting records from this table is complicated.

    I am using a CTE to fetch all child records to be deleted together to avoid foreign key constraint error however it has performance issues and locks the table for long.

    as an alterative I though of :

    ALTER TABLE

    NOCHECK CONSTRAINT FK_XXX

    DELETE FROM TABLE WHERE XXX

    ALTER TABLE CHECK CONSTRAINT FK_XXX

    I want to know the Risks/ drawbacks involved in using this ALTER TABLE NO CHECK CONSTRAINT on production servers.

    Thanks..

    Why are you posting your new question as a reply on a 7 year old thread? You can create a new question using the "Add Topic" button once you entered the correct sub-forum ("Administration - SQL Server 2014", "SQL 2012 - General" or whatever is appropriate for your question).

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply