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

• Jon Ryan

  SSC-Addicted

  Points: 438

  More actions

  April 23, 2007 at 10:37 am

  #103347

  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: 737403

  More actions

  April 23, 2007 at 11:33 am

  #701983

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

• Jon Ryan

  SSC-Addicted

  Points: 438

  More actions

  April 23, 2007 at 11:39 am

  #701988

  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 Veteran

  Points: 206

  More actions

  October 2, 2014 at 4:45 am

  #1749641

  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: 33510

  More actions

  October 2, 2014 at 4:54 am

  #1749645

  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’! **