CODEWITHSUNDEEP
sqlsql-servert-sql
GaryTheBrave
GaryTheBrave

Reputation: 157

How to Deny Disabe Trigger Permissions for a User Group?

I wanted to know the SQL Syntax to Deny Disabling a Trigger for a User Group. There is a crucial trigger that is being disabled and re-enabled by some folks. I want to prevent that from being disabled in the first place. So I tried the following syntax.

USE [MyDBName]
GO
DENY ALTER ON OBJECT::[dbo].[TRIG_SomeName_DELETE] TO [UserGroup];

But i get an error like 'Cannot find the object 'TRIG_SomeName_DELETE', because it does not exist or you do not have permission.'. I have tried executing the above statements from a sql account that has elevated privileges. But I keep getting the same Error. I'm thinking that I might not be using the correct syntax for Denying Disable Trigger Permissions. I'd greatly appreciate any help on this.

Upvotes: 1

Views: 811

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

You need to leave out [dbo]. since a database-level trigger does not belong to a schema. Also, database-level triggers do not have an ALTER permission. You'll have to prevent them from editing any database-level trigger (using the aptly-named ALTER ANY DATABASE DDL TRIGGER permission):

USE [MyDBName];
GO
DENY ALTER ANY DATABASE DDL TRIGGER TO [UserGroup];

This will prevent anyone in UserGroup from creating, dropping, changing, enabling, or disabling any database-level DDL triggers.

Upvotes: 1

Related Questions