Checking for Truncate rights

Question

Checking for Truncate rights

Clive Chinery

SSCrazy

Points: 2563
More actions
August 6, 2009 at 1:10 pm

#95977

With a SQL2005 database, is it possible connecting as a non-administrative user to determine if that user has truncate rights on a table?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Books Online - TRUNCATE TABLE permissions
The minimum permission required is ALTER on table_name. 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. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

There is no explicit TRUNCATE TABLE permission. You probably don't want to grant ALTER permissions on the table just to allow TRUNCATE TABLE so EXECUTE AS seems the way to go?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Clive Chinery SSCrazy Points: 2563 More actions · Answer 2

Thank you. I will just document that the ALTER permission is required.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Clive Chinery (8/7/2009)
Thank you. I will just document that the ALTER permission is required.

This is not what Paul said, read again.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.