Security Questions: TRUNCATE TABLE Permissions

As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security Question

The next question in the list is:

Does [SQL Server] 2012 provide TRUNCATE TABLE permissions?

Yes, permissions to TRUNCATE TABLES is available through the ALTER securable on tables.  This securable has been available since SQL Server 2005, but I can only confirm that it has worked since SQL Server 2008.  The ALTER permission provides the access required to alter the schema of a tables, this includes truncating the data in the table.

Often times, when I tell people about this permission, there is a fear that granting someone ALTER just so that they can truncate a table is too risky.  The user might add or drop a column.  Maybe they’ll even change some data types and break the applications using the table.  All you wanted to do was delete all of the data in the table quickly.

Think about that issue for a moment.  Is the risk of a user changing the schema of a table that much higher than if you grant them permissions to blast away all of the tables data?  If the data in the table is fluid enough that the users can just nuke it, does it matter if schema changes?  What’s more important to the business the data or the schema of the table?  True, if the schema changes, application can break and there can be an extended outage.  Where decimating the data in the table might only cause a temporary outage of data, since the application can start collecting data again and rebuild the businesses data.

In describing how truncation deletes data, I’ve been using a few loaded terms.  There’s a reasons for this.  Truncating the data in a table is the nuclear option for getting rid of data.  True, you can achieve the same effect of truncate with an unfiltered DELETE statement, but that’s a different issue.  When you want someone to be able to TRUNCATE a table, this isn’t going to be a permission that you grant to every table.  Should anyone be able to truncate core business tables, like sales, customer, or product tables?  No, they are going to be truncating tables that are used for activities around staging and importing data.  Does the risk to schema really matter on those tables?

Grant Truncate Permission

With the soap box portion of the post completed, let’s walk through a quick demo on granting TRUNCATE TABLE permissions.  For the demo, we first need to create a login and configure the permissions, using the script provided in Listing 1.  In this script, a new user TruncateMan is created.  Then the ALTER permission is granted to the use in two statements.  The first grants access to a single tables and the second to all of the tables in the schema.  This is useful because it can be easier to manage permissions if all the tables that a user or process may need to truncate are located in one schema.

--Listing 1. Configure user and permissions
USE [master]
GO
CREATE LOGIN [TruncateMan] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

USE [AdventureWorks2012]
GO

CREATE USER [TruncateMan] FOR LOGIN [TruncateMan]
GO

CREATE SCHEMA Stage;
GO

CREATE TABLE Stage.StageStuff (Col1 INT);
GO

CREATE TABLE Stage.StageOtherStuff (Col1 INT);
GO

GRANT ALTER ON Stage.StageStuff TO [TruncateMan];
GO

GRANT ALTER ON SCHEMA::Stage TO [TruncateMan];
GO

To test the permissions, open a new query window and place the script in Listing 2 into that window, while logged in as TruncateMan.  This script runs two TRUNCATE TABLE statements.  The first for the table that specific permissions were granted on and the second which is inherited from the schema permissions.

--Listing 2. Truncate tables
USE AdventureWorks2012
GO

TRUNCATE TABLE Stage.StageStuff
GO

TRUNCATE TABLE Stage.StageOtherStuff
GO

Summary

TRUNCATE TABLE permissions are indeed grantable with SQL Server 2012.  There are risks involved in doing so, but when used on the right tables the risk can be minimized.  Given the conversation in this blog post, would you grant a user the ALTER securable on a table to allow them to truncate data?  Why, or why not?