Permissions for truncating a table

sql-server database-permissions
Shahar Mosek picture Shahar Mosek · Jan 19, 2011 · Viewed 80.4k times · Source

What permission do I need to GRANT a user, in MSSQL, in order to be able to truncate a table?

I'm trying to grant the minimal set of permissions, but I can't use DELETE, because the table is very large, and I want the operation to be quick.

Answer

Shahar Mosek picture Shahar Mosek · Jan 19, 2011

You need the ALTER permission: see the Permissions section here.

Note that you can also use a stored procedure with EXECUTE AS, so that the user running the stored procedure does not need to even be granted the ALTER permission.

Related questions

The error "Login failed for user 'NT AUTHORITY\IUSR'" in ASP.NET and SQL Server 2008

My ASP.NET v3.5 web application is throwing the following exception when it attempts to open a connection to a SQL Server 2008 database: System.Data.SqlClient.SqlException: Cannot open database "MyDbName" requested by the login. The login failed. Login failed …

Read More
SQL Server Disabling All Triggers - Cannot find the object "XXXX" because it does not exist or you do not have permissions

I'm trying to run this command in SQL Server: disable trigger all on MYDB This is failing for me. The account I'm logged into has access to MYDB and I've pretty much giving it every single permission available (it's a …

Read More
How to give access to SQL Server database mail procedures for a Login in a different database?

I'd like to know how to set up permissions within SQL Server to allow my application Login/Role to be able to send email using msdb.dbo.sp_send_dbmail. I have a database MyDb, a user MyUser who is …

Read More