SQL Server Disabling All Triggers - Cannot find the object "XXXX" because it does not exist or you do not have permissions

sql-server permissions database-permissions
Brian Mains picture Brian Mains · Aug 24, 2011 · Viewed 34.7k times · Source

I'm trying to run this command in SQL Server:

disable trigger all on MYDB

This is failing for me. The account I'm logged into has access to MYDB and I've pretty much giving it every single permission available (it's a local DB and my account only, so this is OK). I don't understand why it's telling me it can't find MYDB for this? I've done this before. Also note: I can select from the database, update, and run a grant statement (such as granting execution of a proc). I can also disable triggers manually...

So why does this fail? I was able to do it before...

Thanks.

Answer

Derek Kromm picture Derek Kromm · Aug 24, 2011 
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

Related questions

SQL Server: permissions to read database diagrams

Could you please advise, what are permissions required to give user access to read Database diagrams in SQL Server 2005? Thanks a lot!

Read More
SQL Server Deny all users from a table except for one user

Im not too familiar with SQL Server. Can one of you experts please help me with this ! Ive got a table where I want to stop everyone from making changes (Select is fine) except for one user who can read …

Read More
GRANT EXECUTE to all stored procedures

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database? GRANT EXECUTE TO [MyDomain\MyUser]

Read More