Minimum Permissions for SQL Server TRUNCATE TABLE

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

Problem

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.

Solution

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;
GO 

CREATE USER TestUser WITHOUT LOGIN;
GO 

CREATE TABLE dbo.TestTable (TableID INT);
GO

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

-- Test harness
EXECUTE AS USER = 'TestUser';
GO 

TRUNCATE TABLE dbo.TestTable;
GO 

REVERT;
GO 

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 fails

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';
GO 

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';
GO 

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

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';
GO 

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

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?
REVOKE ALTER ON OBJECT::dbo.TestTable FROM TestUser;
GO 

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

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?
EXECUTE AS USER = 'TestUser';
GO 

DELETE FROM dbo.TestTable;
GO 

REVERT;
GO 

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, January 03, 2019 - 9:05:02 AM - AllegedDBA Back To Top

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

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools