Granting permissions to a user for Truncate Table

We are  migrating from a SQL Server 2005 environment where there were certain IDs that had Sysadmin privileges to a new SQL Server 2008 environment and I have taken away the Sysadmin privileges but I am running into snags with permissions.

I have several SQL logins that need to be able to truncate tables (within an SSIS package) but I cannot get the permissions right.   I have had some success granting access like this:
GRANT ALTER ON OBJECT::schema.tablename TO username;

but it seems tedious to do this for any object where truncate or alter privilege is needed.  How do I Grant a user the proper privilege to be able to truncate tables within a Database?
itsonlyme4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The GRANT ALTER should be enough.
There's any returned error during the execution of the TRUNCATE?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itsonlyme4Author Commented:
on some of the GRANT statements I get this:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

and cannot grant the permission at all
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, I think the message is pretty clear.
You can't grant the permission to yourself and the others users referred by the message already have the maximum permissions so there's no need to grant them any permission.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

itsonlyme4Author Commented:
So then the question becomes, what permissions are needed beyond GRANT ALTER to enable a user to truncate a table?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's only the GRANT ALTER but need to be granted by someone with higher permissions.
0
itsonlyme4Author Commented:
I am using a Sysadmin level account to try and grant a user permissions needed to be able to truncate a table.   Seems that GRANT ALTER on the object to the user is not enough.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the current permissions for that user?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.