Truncating Tables Without Elevated Rights

A common request from users is to receive “Truncate Table Rights” within a database.  Because a truncate statement is a DDL command, this isn’t nearly as easy to fulfill as most people would expect.  Faced with this challenge, a DBA might even find themselves choosing between elevating the user’s rights (e.g. including them into the db_ddladmin or db_owner role) or being a bad guy and flat-out denying the request.  Neither option is a win for both parties, so thankfully (in SQL 2005 and later) there is another solution.

The Win-Win Solution

A quick Google Search will inevitably bring up Bob Horkay’s 2008 post on SQLServerPedia which is a generic custom permission set for issuing Truncates.  If you choose to execute Bob’s code verbatim, you will create a stored procedure and a new schema containing two tables.  Once you create the objects, you will need to populate the control table with a list of tables you’re allowing to be truncated.   You then grant EXECUTE permissions on the stored procedure to the user(s) receiving Truncate Table rights, after which users execute the stored procedure and tables start getting truncated.

It’s relatively straight forward, but what if you don’t want/need it to be that complex?  That’s where my modifications come to the rescue.  I’ve boiled down Bob’s code to a single SP that allows the caller to truncate any table in either the dbo or their own schema.  Under most situations where you would receive this sort of request (e.g. Development/Test environments), this is really all that is needed:

CREATE PROCEDURE [dbo].[TRUNCATE_TABLE]
(
    @TableName VARCHAR(257)
) WITH EXECUTE AS SELF
AS
BEGIN
    -- Only Allow Truncates to dbo schema and user's own Schema
    IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s
                    ON t.schema_id = s.schema_id
                   WHERE (s.name + '.' + t.name = @TableName
                        OR s.name + '.' + t.name = ISNULL(SCHEMA_NAME(), 'dbo') + '.' + @TableName)
                        AND s.name IN ('dbo', ISNULL(SCHEMA_NAME(), 'dbo')))
    BEGIN
        DECLARE @ReturnCode INT
        DECLARE @DynamicSQL NVARCHAR(272)

        SET @DynamicSQL = 'TRUNCATE TABLE ' + @TableName

        -- Initialize @ReturnCode variable
        SET @ReturnCode = -1

        BEGIN TRY
            -- Attempt Truncate Operation
            EXEC @ReturnCode = sp_executeSQL @DynamicSQL
        END TRY
        BEGIN CATCH
            -- If an Error is encountered, trap it
            DECLARE @ErrorMessage NVARCHAR(2048), @ErrorNumber INT, @ErrorSeverity INT,
                @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(126);

            SELECT @ErrorNumber = ERROR_NUMBER(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE(),
                   @ErrorLine = ERROR_LINE(),
                   @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '');

            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, PROCEDURE %s, Line %d, ' +
                'Message: ' + ERROR_MESSAGE();

            -- Raise an error: msg_str parameter of RAISERROR will contain
            -- the original error information.
            RAISERROR(@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity,
                      @ErrorState, @ErrorProcedure, @ErrorLine)

            RETURN @ReturnCode
        END CATCH
    END

    ELSE
    BEGIN
        -- If user is trying to Truncate a table they are not allowed to, throw and error.
        RAISERROR('Cannot find the object because it does not exist or you do not have permissions.'
                ,16, 1, 1088, 16, 7, '[dbo].[TRUNCATE_TABLE]', 1)
        RETURN -1
    END
END

So how does this all work?

As Bob briefly mentions in his post, the EXECUTE AS clause of the CREATE PROCEDURE command is where the magic lies.  Basically, if a user has the ability to create stored procedures, chances are that user will have the ability to truncate tables.  When a stored procedure is created using the WITH EXECUTE AS SELF clause, the code within it will always run with the elevated security context of the user that it was created by.  This implicit context switching is what allows a seemingly basic user the ability to execute a DDL command that they wouldn’t be able to do directly.

John

References (in order of appearance):
http://sqlserverpedia.com/blog/sql-server-bloggers/grant-truncate-table-permissions-in-sql-server/
http://msdn.microsoft.com/en-us/library/ms190384.aspx
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms191296.aspx

Advertisement