Giving users specific DDL permissions

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 FineganK Finegan (KevinFinegan@.Hotmail.com) writes:
> 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?

In SQL2000, yes. The upcoming version of SQL Server has some more
possibilities.

As for the triggers, it's probably better to say ALTER TABLE DISABLE
TRIGGERS ALL, than to drop them. Not that this addresses the permissions
problem.

There is a way to have a trigger off-turnable by means of regular
permissions though. In the trigger body you do this:

IF object_id('tempdb..#reloading') IS NULL
... Trigger logic comes here.

In you process you would create this temp table. The test would save you
the logic of the trigger, but you may still have an overhead, because
SQL Server has to run the statement as if there trigger was active.

No, for indexes I don't have any tricks.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I like the trigger trick, will probably use it &
DISABLE which I didn't know about. Pity about the indexes. For the
moment I suppose I'll have to give one (trustworthy) user ddl_admin
rights.

K Finegan