How do I - Allow someone the ability to TRUNCATE TABLE in SQL Server without granting them DBO rights?

There comes a time where a developer produces a stored procedure (sproc) that tries to clear a table quickly. In the case of permanent tables, this is usually done using the TRUNCATE TABLE statement in TSQL. Of course in a production setting, you will have limited rights on your SQL Server and databases. As such, we generally grant users DB_DATAREADER and DB_DATAWRITER rights. Additionally, we'll grant users EXECUTE rights on sprocs. The problem is that in order to do a TRUNCATE TABLE, SQL actually drops that table in order to clear it so quickly. That won't be possible when a users is trying to execute the sproc in production. Since they have EXECUTE rights, they'd be able to execute it, but it will fail when the system tries to drop the table in question. For that, the executor needs to have DBO rights. Of course we're not going to grant people DBO rights in production! So the question becomes, how do you solve the problem?

I'm assuming that well defined change management and process control is in place already. If it is not, you need to start there first. Once you have a proper method for reviewing changes to sprocs going into production, you can apply this quick fix:

TSQL BEFORE

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
AS    
DECLARE @QUERY NVARCHAR(200);
...

TSQL AFTER

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
WITH EXECUTE AS OWNER
AS
DECLARE @QUERY NVARCHAR(200);
...

By simply adding the WITH EXECUTE AS OWNER line to the sproc, it will execute as owner level permissions, thus allowing the TRUNCATE statement to succeed.  Now it's important to understand that this creates a security risk that YOU must manage.  That's why it's important to review any and all changes that are made to the sprocs, especially if they're going to have this statement in them.  It may be better to just create a generic sproc that you can feed a table name to, which elevates like this and truncates the table passed to it.

Enjoy
C