SQL Server Permissions – Necessary Rights for Truncating a Table

permissionssql serversql-server-2012truncate

I have a SQL account with the following permissions on a database:

enter image description here

The db_executor role you see this account being a member of was created by this script:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor] 
GO

When I run a select, update, insert or delete on the table, it works fine. When I try to truncate the table, it gives me this error message:

Cannot find the object "TableName" because it does not exist or you do not have permissions.

What permission is this account missing?

Best Answer

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates:

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.

So ALTER is the minimum permissions required. You can get that as DB Owner, you can get that as DB_DDLAdmin. Or just grant alter.

If you think about what truncate does and how it works, this makes sense, it is a pretty "severe" command and empties the table of data and does it quickly.

Related Question