Minimum Permissions for SQL Server TRUNCATE TABLE

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 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.

Next Steps

• Read up on one technique to grant TRUNCATE TABLE through stored procedures.
• Learn how you can cause stored procedures to execute in a different security context.
• See a method for truncating all the tables in a given database.