I have an archival process on a large database that runs once a month.
At the beginning of the process the triggers and indexes on the
tables whose data is moved are dropped, the data is moved and then the
triggers and indexes are recreated at the end. This produces a
massive improvement in performance.

The problem is the process is supposed to run on users accounts (thats
the way the front-end is set up) and they don't have the neccessary
permissions to drop & create triggers & indexes. I can't see any way
to give them permissions only on specific tables or triggers/indexes.
Nor does giving them permissions to the stored procedures that do the
dropping & re-creating work, DDL permissions don't seem to be
inherited the way they are with tables.

Is blanket rights to drop & create objects through the db_ddladmin
role the only way users can get rights?

Thanks,

K Finegan