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.
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:
- 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.
- 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.
- 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.
- 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:
- Granting permission with the EXECUTE AS command in SQL Server by Greg Robidoux
- Using EXECUTE AS to Create Custom Permissions Sets – Microsoft Books Online I did not use EXECUTE AS SELF as referenced in this link because I wanted as few permissions as possible associated with the store procedure.
- Giving Permissions through Stored Procedures by Erland Sommarskog, SQL Server MVP. Discussed that various ways of using EXECUTE AS.
