March 5, 2006 at 11:26 am
Background: All table objects are owned by dbo. We want to create a batch id which will perform DML on these tables. Part of the process is to truncate the tables prior to inserts.
Security concern: granting db_ddladmin gives the batch id too many privileges.
Option: Create stored procedures to truncate the tables owned by dbo and grant execute to batch id role.
Is this feasible?
Brian Smith
March 6, 2006 at 8:45 am
If it's 2005, EXECUTE AS, or (and in 2000) you could schedule it, and it'll run as the Agent.
March 6, 2006 at 9:21 am
Joseph, Thanks for the reply.
Is it possible to create a truncate procedure owned by dbo and then give execute privilege to a batch id. This would leave ownership with dbo and not the batch id.
Brian Smith
March 8, 2006 at 1:56 am
The object can belong to any schema including dbo, the point is to include the EXECUTE AS dbo in the procedure, then grant execute on the procuedure to the batch id.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply