Truncate table/create index permissions with dynamic sql
Posted on 2007-03-20
I need to be able to use dynamic sql to truncate tables and create indexes. Two different stored procs are involved. One simply does a truncate table, using dynamic sql and a table name parameter. The other just creates an index on a column, using dynamic sql and an a table name parameter. This is related to a batch process; there are a group of identical tables involved.
The problem is that once the stored proc uses dynamic sql, there is not sufficient permission to do the truncate or create index. How do I assign permissions so that the batch process account can do the truncate and create index?