February 1, 2007 at 4:46 am
I have a nightly script job that runs some maintenance jobs for my site.
One of these jobs calls a stored proc that needs to rebuild a mapping table from scratch. I want to be able to truncate this table but obviously the user needs high permissions to do this. At the moment the user that the script connects to the DB with has only datareader, datawriter and execute permissions on any procs I use.
What is the best way to be able to accomplish this in 2000, I know in 2005 you can use "with execute as "
I don't want to have to create a separate user for this connection with higher privileges. So can someone recommend the best way to achieve what I need?
Thanks in advance for any help.
February 1, 2007 at 5:28 am
Permissions needed (from the books online):
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
Is is necessary that it is done from scratch? Perhaps joining on primary keys ...
February 1, 2007 at 7:26 am
Yes I read that in BOL as well. But I thought you might be able to GRANT the user specific permissions just for that proc. I thought maybe theres a bodge that can acomplish what SQL 2005 does with ease.
The reason it needs to be build from scratch each night is because admin might be adding/removing new records to the base tables throughout the day (which happens very frequently) and I thought rebuilding it would be better than having to have lots of convaluted triggers on the related tables to keep the mapping table related. Lots of different sites are linked together in parent/child relationship and the mapping relates child categories to parent categories if they exist in the related site.
As the job using the mapping table only runs once a day it seems logical to just build the table before hand. There are no identity fields that need to be refreshed so I could just do a DELETE with no where clause but I would like to do a truncate if its possible. If its not then I can't.
February 1, 2007 at 9:56 am
There are *statement* level permissions and *object* level permissions. Truncate table requires the former and therefore can't be bypassed. If this is a recurrent task you could create a job that runs with elevated privileges and nothing has to be done manually.
Otherwise use delete.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply