Grant Truncate Table Permissions in SQL Server without ALTER Table

By:   |   Updated: 2012-01-03   |   Comments (5)   |   Related: > Security


Problem

How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table?  Check out the examples and explanation in this tip.

Solution

The TRUNCATE TABLE SQL statement is a great time and log space saver when deleting all rows from a large table. However to use this statement, the executing user requires ALTER permission on the table, which is contrary to the best practices of limiting permissions. To over come this, there is an option available to allow a developer to truncate a table, but not have ALTER permission. This is accomplished by a DBA creating a stored procedure with the TRUNCATE TABLE statements and then granting execute permission on the stored procedure to those who need it.

In addition to the permission restriction, several other rules prevent the usage of TRUNCATE TABLE. These rules are listed below.

  • Table referenced by a FOREIGN KEY constraint.
    • You can truncate a table that has a foreign key that references itself.
  • Table that is a participant in an indexed view.
  • Table published by using transactional replication or merge replication.

The FOREIGN KEY restriction is only for the parent table. A stored procedure can be used to truncate a table and overcome the FOREIGN KEY restriction. This is accomplished by including drop/create foreign key SQL with the TRUNCATE TABLE statements. This article provides stored procedure samples to address this challenge and other types of foreign key configurations.

Preparation

Create the sample database, TestSQL, using this script SampleDB_TestSQL.sql. The database contains five populated tables used to demonstrate how a stored procedure can be used to truncate tables using one of these four FOREIGN KEY configurations:

  • no foreign key
  • self-referencing
  • child only
  • parent with child tables

Step 1 - Create User Proxy ID

The main difference between a SQL id and a user proxy id is that the proxy id cannot log into the database because no login is created for it.

Use the following scripts to create the User Proxy ID and grant permission on the table.

-- Create User Proxy in the User Database 
USE [TestSQL]
GO
CREATE USER [truncate_proxy] WITHOUT LOGIN;
GO
GRANT ALTER TO [truncate_proxy];
GO

After you create the proxy id, connect to the instance using SQL Server Management Studio (SSMS) and you will see the user proxy id listed as a user for the database but not as an instance login. In addition, the id can only be used by those granted permission to IMPERSONATE it or have SYSADMIN permission. This user proxy id should only be used in truncate table stored procedures to prevent confusion of its purpose. SYSADMIN's by default are the only ones who can create a stored procedure that uses this id and hence retains full control over which tables can be truncated and by whom.

Proxy User listed as USER but not LOGIN

Step 2 - Create Stored Procedure with TRUNCATE TABLE permission

A truncate table stored procedure requires only a few SQL statements when the table has no restrictive foreign keys. The EXECUTE AS statement specifies the user proxy whose permission is used while the stored procedure is executing. The TRUNCATE TABLE identifies the table to be truncated. The SELECT statement in this stored procedure sample is used to show the specified EXECUTE AS 'user id' is being used. Once you are confident the correct permission is being used, remove it.

-- Create Stored Procedure
CREATE  PROCEDURE dbo.Truncate_Table_Loner 
 WITH EXECUTE AS 'truncate_proxy' 
   AS
-- Remove this select, it is only used to show the USER being used during execution.
   SELECT SYSTEM_USER [SYSTEM_USER], USER [USER], name, type, usage 
     FROM sys.user_token;
   TRUNCATE TABLE [dbo].[Loner];
GO

After the stored procedure is created, perform a quick test by executing it using the test script below. The BEGIN TRANS and ROLLBACK statements are used to keep the data changes from being committed and allow you to repeat the test. This script also demonstrates that TRUNCATE TABLE can be rolled back.

-- Quick Test
BEGIN TRAN
 SELECT COUNT(*) CNT FROM [dbo].[Loner]; 
 EXEC dbo.Truncate_Table_Loner;
 SELECT COUNT(*) CNT FROM [dbo].[Loner]; 
ROLLBACK;
GO

Step 3 - Grant Execute Permission

Create a test Login and User id then grant it execute permission on the stored procedure Truncate_Table_Loner. This id will be used to perform the truncate.

-- Grant Execute Permission
-- Setup ID on Database with Connect permission
USE master 
GO
CREATE LOGIN [test_user_id] WITH PASSWORD = 'JustConnect123';
GO
USE TestSQL
GO
CREATE USER [test_user_id] FOR LOGIN [test_user_id];
GO
-- Grant Permission
GRANT EXECUTE ON dbo.Truncate_Table_Loner TO [test_user_id];
GO

Note: Since this id can connect to the database, it will also have permission to anything granted to PUBLIC in the database and instance

Step 4 - Execute Stored Procedure as Test User

As SYSADMIN, one can IMPERSONATE a Login, in this test that is just what we do. The test user id has been granted execute on the stored procedure. Using the EXECUTE AS we IMPERSONATE "test_user_id" and execute the stored procedure.

This test script besides executing the stored procedure, displays the user name being use before each SQL execution. The REVERT command switches the connection back to your user id.

-- Test Truncate
BEGIN TRAN
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[Loner]; 
 EXECUTE AS LOGIN = 'test_user_id';
 SELECT COUNT(*) CNT FROM [dbo].[Loner]; -- Denied because id does not have select
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 EXEC dbo.Truncate_Table_Loner;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 REVERT;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[Loner]; 
ROLLBACK;
GO

Step 5 - Truncate Foreign Key Child Only Table

Truncating the "child" table in a foreign key relationship is no different than truncating a table with no foreign keys. The only change in the stored procedure is the table being truncated.

-- Child Only FK
CREATE  PROCEDURE dbo.Truncate_Table_ChildTable2 
 WITH EXECUTE AS 'truncate_proxy' 
  AS
-- Remove this select, its just used to show which USER is being used during execution.
   SELECT SYSTEM_USER [SYSTEM_USER], USER [USER], name, type, usage FROM sys.user_token;
   TRUNCATE TABLE [dbo].[ChildTable2];
   
GO
-- Grant Permission
GRANT EXECUTE ON dbo.Truncate_Table_ChildTable2 TO [test_user_id];
GO

Test this stored procedure by running the following script.

-- Test Truncate
BEGIN TRAN 
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[ChildTable2]; 
 
 EXECUTE AS LOGIN = 'test_user_id' SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 EXEC dbo.Truncate_Table_ChildTable2;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 REVERT;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[ChildTable2]; 
ROLLBACK;
GO

Step 6 - Truncate Self-Referencing Table

The syntax for truncating a self-referencing foreign key table is again the same as a table without any foreign keys. The only change in the stored procedure is the table being truncated.

-- Self Referencing FK
CREATE  PROCEDURE dbo.Truncate_Table_SelfRefTable 
 WITH EXECUTE AS 'truncate_proxy' 
  AS
-- Remove this select, its just used to show which USER is being used during execution.
   SELECT SYSTEM_USER [SYSTEM_USER], USER [USER], name, type, usage FROM sys.user_token;
   TRUNCATE TABLE [dbo].[SelfRefTable];
GO
-- Grant Permission
GRANT EXECUTE ON dbo.Truncate_Table_SelfRefTable TO [test_user_id];
GO

Test this stored procedure by running the following script.

-- Test Truncate
BEGIN TRAN
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[SelfRefTable]; 
 
 EXECUTE AS LOGIN = 'test_user_id';
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 EXEC dbo.Truncate_Table_SelfRefTable;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 REVERT;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT COUNT(*) CNT FROM [dbo].[SelfRefTable]; 
ROLLBACK;
GO

Step 7 - Truncating Parent Table with Child Tables

TRUNCATE TABLE statement will not work on a table that is the parent table in a Foreign Key to other tables. Disabling foreign keys will not work. Truncating child table first will not work. You have to remove the foreign keys for it to work. This can be accomplished in a stored procedure.

Here are the steps to complete this task:

  1. Grant the user proxy REFERENCE permission on the database. The reference permission is required to add FOREIGN KEYS. The ALTER permission is not enough. It gives permission to DROP FOREIGN KEYS but not ADD them.
  2. Add ALTERS to drop foreign keys on the child tables. Dropping the child table FOREIGN KEYS will allow the TRUNCATE TABLE to work on the parent table.
  3. Truncate child tables along with the parent table. The child tables needs to be empty before adding back the FOREIGN KEYS since the parent table is empty.
  4. Add ALTERS to create foreign keys on the child tables. This puts back the dropped foreign keys. It should run quickly since all the involved tables are empty.
-- Grant Reference permission in the Database to allow creation of foreign keys
USE [TestSQL]
GO
GRANT REFERENCES TO [truncate_proxy];
GO
-- Truncate Parent table with child tables 
CREATE PROCEDURE dbo.Truncate_Table_Parent_and_Child_tables 
  WITH EXECUTE AS 'truncate_proxy' 
   AS
-- Remove this select, its just used to show which USER is being used during execution.
   SELECT SYSTEM_USER [SYSTEM_USER], USER [USER], name, type, usage FROM sys.user_token;
   ALTER TABLE [dbo].[ChildTable1] DROP CONSTRAINT [FK_ChildTable1_ParentTable];
   ALTER TABLE [dbo].[ChildTable2] DROP CONSTRAINT [FK_ChildTable2_ParentTable];
   TRUNCATE TABLE [dbo].[ChildTable1];
   TRUNCATE TABLE [dbo].[ChildTable2]; 
   TRUNCATE TABLE [dbo].[ParentTable]; 
   ALTER TABLE [dbo].[ChildTable1]  WITH CHECK 
         ADD  CONSTRAINT [FK_ChildTable1_ParentTable] FOREIGN KEY([Child_PK_id])
         REFERENCES [dbo].[ParentTable] ([Parent_PK_id]);
   ALTER TABLE [dbo].[ChildTable2]  WITH CHECK 
         ADD  CONSTRAINT [FK_ChildTable2_ParentTable] FOREIGN KEY([Child_PK_id])
         REFERENCES [dbo].[ParentTable] ([Parent_PK_id]);                  
GO
-- Grant Permission
GRANT EXECUTE ON dbo.Truncate_Table_Parent_and_Child_tables TO [test_user_id];
GO

Test this stored procedure by running the following script.

-- Test Truncate on Parent table
BEGIN TRAN
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT 'Parent', COUNT(*) CNT FROM [dbo].[ParentTable] 
  UNION ALL 
 SELECT 'Child1', COUNT(*) CNT FROM [dbo].[ChildTable1] 
  UNION ALL 
 SELECT 'Child2', COUNT(*) CNT FROM [dbo].[ChildTable2]
 ; 
 
 EXECUTE AS LOGIN = 'test_user_id';
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 EXEC dbo.Truncate_Table_Parent_and_Child_tables;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 
 REVERT;
 SELECT SUSER_NAME() [SUSER_NAME], USER_NAME() [USER_NAME];
 SELECT 'Parent', COUNT(*) CNT FROM [dbo].[ParentTable] 
  UNION ALL 
 SELECT 'Child1', COUNT(*) CNT FROM [dbo].[ChildTable1] 
  UNION ALL 
 SELECT 'Child2', COUNT(*) CNT FROM [dbo].[ChildTable2]
 ; 
ROLLBACK;
GO
Next Steps
  • You have now created stored procedures that allow users and jobs to truncate tables without granting them ALTER permission. The principal benefit for using a TRUNCATE TABLE statement is to delete quickly all rows in a table with a minimum of logging. If a DELETE runs quickly, then I would not bother with this process. However, when speed is an issue this can be the solution you need.
  • While testing these scripts, if you find you have lost your SYSADMIN permission, try executing a REVERT statement.
  • The scripts in this tip have been tested on SQL Server 2005 and SQL Server 2008.
  • Check out these additional resources:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author David Bird David Bird has years of IT Experience working as a DBA and programmer on Windows, UNIX, and mainframes.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-01-03

Comments For This Article




Thursday, September 21, 2017 - 4:59:29 PM - Yimy Asprilla Back To Top (66499)

You can create a stored procedure with execute as owner to only one table or a stored procedure to any table. In the next code is stored procedure to truncate any table without assinging permission of db_owner or other. In this version of SP is included handling of errors and prevention of SQL Injection

SET ANSI_NULLS ON

 

 

 

 

GO

SET QUOTED_IDENTIFIER ON

 

 

 

 

GO

 

 

 

　

 

　

 

　

/****** Se validan el parametro de entrada @strTabla para evitar un SQL inyección, Yimy Asprilla ******/

CREATE PROCEDURE [dbo].[spTruncate]

 

@strTabla VARCHAR(100)

 

WITH EXECUTE AS OWNER

 

 

 

 

AS

-- =============================================

-- Author: Yimy Orley Asprilla

 

-- Create date: Julio 16 de 2014

 

-- Update: September 21 2017

 

-- Description: Función para hacer TRUNCATE a una tabla si ser owner de la tabla. con manejo de errores y SQL Inyection

 

-- =============================================

 

SET NOCOUNT ON

 

DECLARE @strSQL VARCHAR(500);

 

DECLARE @object_id int;

 

SET @object_id = OBJECT_ID(@strTabla);

 

BEGIN TRY

 

IF @object_id IS NOT NULL

 

BEGIN;

 

BEGIN TRANSACTION;

 

SET @strSQL = 'TRUNCATE TABLE [' + @strTabla + '];'

 

EXECUTE (@strSQL);

 

COMMIT TRANSACTION;

 

END;

 

ELSE

 

BEGIN;

 

PRINT N'La Tabla: ' + @strTabla + ' No existe';

 

END;

 

END TRY

 

BEGIN CATCH

 

-- se presento un error en la ejcución y s epresenta

 

PRINT N'Se presento el error: ';

 

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

 

END CATCH;

 

Friday, July 18, 2014 - 4:24:21 PM - Yimy Back To Top (32781)

You can create a stored procedure with execute as owner to only one table or a store procedure to any table. In the next code is  SP to truncate any table without assing permission of db_owner or other:

 

USE [database name]
GO
/****** Object:  StoredProcedure [dbo].[spTruncate]    Script Date: 18/07/2014 03:16:37 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
CREATE PROCEDURE [dbo].[spTruncate]
 @nameTable varchar(60) 


WITH EXECUTE AS OWNER
AS
 
 SET NOCOUNT OFF;

    DECLARE @QUERY NVARCHAR(200);
 
 SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'
 

 EXECUTE sp_executesql @QUERY;
 

Thursday, March 15, 2012 - 10:13:26 AM - ScottPletcher Back To Top (16408)

Good article and approach, and very thorough.

Still, I'd be too nervous about potential future changes to hard-code the FK definitions.  I would rather dynamically capture them and re-create the FKs based on their current definition.

Tuesday, January 3, 2012 - 2:57:22 PM - Ray Back To Top (15505)

I like the approach and the implementation.

I wonder, should you really truncate the child tables?  The Foreign Key relationship is not always mandatory (i.e., 0 or more) and in that case it is likely that it would be preferable to delete the child rows where the FK value is not null rather than simply truncate the table.

Of course, this can become a real hassle if the key relationships are nested or even recursive :).

In a previous life, I started on a script that would save the definition of every FK that referenced a table and then drop the FK.  A later part of the script would re-create the FK.  It got complicated. ;P

Tuesday, January 3, 2012 - 8:25:48 AM - RichS Back To Top (15501)

I really never like to give anyone truncate rights, but this is a great set of scripts and examples.














get free sql tips
agree to terms