What is a minimum SQL Server role allowing trigger creation/dropping?

Question

Prerequisite:

There is client/server application written in Delphi32. The RDBMS is SQL Server 2005. A certain application functionality requires creation/dropping of triggers (from the application using ad hoc DDL statements) in the target database.

Problem:

If a user belongs to roles sysadmin there is no problem to create/drop triggers. However, this is considered to be a too permissive role for an application user.

Questions:

• What is the standard minimum (i.e. most restrictive) SQL Server role that could be used for creation/dropping of triggers?
• Would creation of a custom role, rather than using standard ones, constitute a more fine-grain approach to solving this problem?

Answer 1

There are various options depending on the scope of the grant (any trigger in a database, any in a schema or a specific table).

Generally the permissions required for a SQL operation are documented in the SQL reference. E.g. for CREATE TRIGGER on SQL 2008 see here (scroll down to "Permissions"), in this case:

DML trigger requires ALTER persmission on the table or view on which the trigger is to be created

You can create a role granted just the permissions you want and then add users/groups to that role, so the minimum role is one you create with just the specific access you want to allow its members.