Minimum Permissions for SQL Server TRUNCATE TABLE

By:   |   Updated: 2015-12-01   |   Comments (3)   |   Related: More > T-SQL

Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


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

Last Updated: 2015-12-01

get scripts

next tip button

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

View all my tips

Comments For This Article

Tuesday, January 05, 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 03, 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.


Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor

get free sql tips
agree to terms