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

Question

Post reply

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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: 33507 More actions · Answer 4

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