February 11, 2009 at 2:06 pm
So I have been racking my brains on what I thought was a proxy account problem but to find out it is within SQL and the way to clear out a table that is my problem.
Trying to go with best practices I use a TRUNCATE TABLE command before loading data into HOPE_PJCT_LOAD table.
I have a user called DBSBATCH that runs the script (combined with xp_cmdshell) and it fails indicating that
"Cannot find the object "HOPE_PJCT_LOAD" because it does not exist or you do not have permissions."
So I look at permissions for DBSBATCH on table HOPE_PJCT_LOAD and it has SELECT, DELETE, UPDATE, INSERT, VIEW DEFINITIONS
Now if I switch the command to DELETE FROM HOPE_PJCT_LOAD it runs successful.
So TRUNCATE TABLE and DELETE have two levels of security. So do I grant ALTER permissions to DBSBATCH to use the TRUNCATE TABLE command?
February 11, 2009 at 3:29 pm
alter permissions on the table would be required or one of the built in database roles db_owner or ddl_admin. All of these provide way to many permissions just to truncate a table. Check BOL for the section "Using EXECUTE AS to Create Custom Permission Sets"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply