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
That’s quite awesome! This is great news for developers in DBA-controlled shop.
The EXECUTE AS is great for so many other reasons too.