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
Setup Truncate Table Example
First, create a user in a test database, along with a test table:
CREATE USER TestUser WITHOUT LOGIN;
CREATE TABLE dbo.TestTable (TableID INT);
Now we'll write a test harness to test as this user we've created:
-- Test harness
EXECUTE AS USER = 'TestUser';
TRUNCATE TABLE dbo.TestTable;
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?
REVOKE ALTER ON OBJECT::dbo.TestTable FROM TestUser;
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?
EXECUTE AS USER = 'TestUser';
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.