Permissions required to Truncate

Question

Permissions required to Truncate

shailendra-251422

Ten Centuries

Points: 1111
More actions
March 9, 2006 at 6:19 am

#68771

Hi,
What is the least privileged role that a user needs to truncate a table. The delete permission does not seem to have a control over truncate command. I need to allow the batch operator to be able to truncate the staging tables. This opertator should have the minimum pernmissions. This is for DTS on SQL 2K
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

I think the minimum you need is for your operators to be a member of db_ddladmin however this role probably has too many priveliges for your liking. You could put the staging table in a seperate db where the operators can have access.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Truncate permission also defaults to the table owner. If the operator were the owner of the staging tables he or she could truncate them but not others in the database.

Greg

Dick Schroth Right there with Babe Points: 725 More actions · Answer 3

I would think it might be a better idea to create a stored procedure to truncate, and give the operator execute permission on the SP.

Dick

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

Dick, I don't think that will work any more with service pack 3 - you can't grant permission to truncate from within an sp.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

'truncate' is a DDL command and has never been 'grant-able'. ddl-admin role will work since 'truncate' falls into that category.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

jnaumann Valued Member Points: 71 More actions · Answer 6

To allow truncate you can add 'WITH EXECUTE AS OWNER' to the procedure.

😉

Joerg

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

jnaumann (12/3/2008)
To allow truncate you can add 'WITH EXECUTE AS OWNER' to the procedure.
😉
Joerg

...but only in SQL 2005.

Greg

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 8

Hi,

The user need to be member of db_ddladmin at least.

http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

Regards

shailendra-251422 Ten Centuries Points: 1111 More actions · Answer 9

thanks everyone 🙂

I had posted the question in 2006, and have got three replies in the last two days!! Appreciate your help.

ashnir SSC Enthusiast Points: 130 More actions · Answer 10

If you give user CONTROL permission on that table( if its just some table in SP scope) than it should be enough.

ashnir SSC Enthusiast Points: 130 More actions · Answer 11

If you give user CONTROL permission on that table( if its just some table in SP scope) than it should be enough.