Minimum Permissions for SQL Server TRUNCATE TABLE

By:   |   Comments (6)   |   Related: > TSQL


I have read over the Books Online entries for the SQL Server TRUNCATE TABLE command and I'm confused. On the one hand it sounds like if the user has ALTER permissions on the table, that's sufficient to successfully issue the command. However, it also sounds like only being a member of db_owner or db_ddladmin works. Which is it? I don't have the option of wrapping the statement in a stored procedure, so I need the actual rights necessary to issue the command.


The user must have ALTER permissions against the table. While being a member of db_owner or db_ddladmin certainly works, unless the user must have the full permissions those roles provide, making the user a member of such role is a violation of the Principle of Least Privilege. We can test this minimal permission quite easily.

Setup Truncate Table Example

First, create a user in a test database, along with a test table:

USE Test;


CREATE TABLE dbo.TestTable (TableID INT);

Now we'll write a test harness to test as this user we've created:

-- Test harness



If you execute the test harness, you'll get an error. This is because the user doesn't have permissions to issue the TRUNCATE TABLE command:


Truncate Table Using Database Roles

With the setup complete, let's verify what we know, that db_owner works. Execute the following to make the user a member of db_owner and then go and execute the test harness.

-- Make the user a member of db_owner
-- We know this works
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'TestUser';

Next, let's try db_ddladmin. First we'll revoke membership from the db_owner role and then grant membership in the db_ddladmin role.

-- Remove user from db_owner and add to db_ddladmin
-- We know this works, too
EXEC sp_droprolemember @rolename = 'db_owner', @membername = 'TestUser';

EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = 'TestUser';

Try the test harness and you'll see it works as expected.

Granting ALTER permissions to Truncate Table

Now it's time to test out the ALTER permission. Obviously, even the ALTER permission on the table is one that we don't like, because it gives more access than what we want, which is to simply be able to issue the TRUNCATE TABLE statement. Ideally, we'd wrapper this in a stored procedure and give access to the stored procedure, but if that's not doable, this is the minimum permission Books Online indicates will work. So let's revoke membership from db_ddladmin and grant ALTER on the table to our test user. Now ideally, if you were designing security for an application, you'd create a role, apply security to the role, and then make the user a member of the role. However, for this example I'm skipping the role part to cleanly identify the permission needed:

-- Books Online said having ALTER permissions works
-- Let's remove the user from db_ddladmin and try that
EXEC sp_droprolemember @rolename = 'db_ddladmin', @membername = 'TestUser';

GRANT ALTER ON OBJECT::dbo.TestTable TO TestUser;

And that works!

Granting DELETE permissions to Truncate Table

Let's attempt one final test, one we know should fail. Let's revoke the ALTER permission and grant DELETE.

-- What about straight DELETE rights?

GRANT DELETE ON OBJECT::dbo.TestTable TO TestUser;

When we execute the test harness, we get the same error as before, when the user had no permissions at all. However, since the error indicates the object might not exist, let's build a second test harness, this time using DELETE and see what happens:

-- Truncate table gave an interesting error. What about straight DELETE?

DELETE FROM dbo.TestTable;


And when we execute this second test harness, we will have success. The table is there and we have verified what we already knew: DELETE permissions isn't sufficient to execute TRUNCATE TABLE.

DELETE works
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, August 18, 2022 - 4:45:04 PM - Mel Vargas Back To Top (90388)
Thanks! This really helped me.

Thursday, February 24, 2022 - 12:42:21 PM - Tonya13 Back To Top (89830)
Great write up. I know it is a slightly older post but this might help someone so I am commenting anyway. :) When a DELETE is ran, every row deleted is written to your transaction log. So, if you run Delete from [Table] you risk filling up your log file when dealing with large tables. In turn, it can cause space issues when the large grows or a hung database if the log cannot grow. With Truncate, nothing is written to the log.

If you know 100% that the data will never be needed, run TRUNCATE but if there is a chance that the data may need to be restored or if you are not sure, run the DELETE (or a backup before you run Truncate). If you run a Delete on a large table, you can also do it within a transaction and commit after X number of rows... That will help keep your log growth down and is really a better practice anyway. :)

Tuesday, November 9, 2021 - 11:10:33 AM - AndyB Back To Top (89429)
One thing that I would add, is that wrapping the TRUNCATE TABLE within a stored procedure does not seem to work. I got this error when I called a stored procedure that had a TRUNCATE TABLE command. I was double confused by the error message because the account under which the procedure did have INSERT, SELECT, UPDATE, and DELETE rights to the table in question. The SQL version I was using for this is SQL2012

Tuesday, January 5, 2021 - 9:28:50 AM - Mike Back To Top (88002)
One reason that ALTER TABLE is needed is that TRUNCATE TABLE reseeds any identity columns.

Thursday, January 3, 2019 - 9:05:02 AM - AllegedDBA Back To Top (78616)

Thank you for helping to clear up the confusion around the Truncate Table command.

Is this behavior consistent across all versions of SQL Server?

Wednesday, May 24, 2017 - 9:04:59 PM - Cat45 Back To Top (56022)

Easy to follow, simple explanation that set me well on the right path.  Many thanks.

get free sql tips
agree to terms