This has been a thorn in my side as long as I have worked with SQL Server.  There are times you want to grant truncate table permissions, either on a table or all tables etc to a specific user and/or service account, BUT you do not want to grant any alter or DDL privlidges or worse db owner!

Now why Microsoft, in SQL Server 2005 did not make this available, I do not know; as smart as the people who I have seen and met that work on the product, I'm sure they could have overcome any technical issue given them.  And please don't use ANSI Standard or some other weak excuse.  I want to be able to type "Grant Truncate on Table to User", just like Select, Update or Delete.  Too BAD for me.

Fortunately Micro$oft did give us a "fishing pole", so go learn how to fish and explore: execute as.  With this procedure you can delegate and impersonate to accomplish tasks. 

So the basic setup we devised, (which was based on a model used by our Oracle group):

1. Stored procedure - using Execute As to elevate rights
2. Control Table - to determine which tables a user is authorized to truncate
3. Audit Table - who, what and when (could be enhanced for why !)

The scripts below need a slight review and may need work for you particular enviornment, but the "guts" of the process is here.  Note that the control and audit table are in a dedicated DBA Schema.

USE {database}

CREATE SCHEMA [dba] AUTHORIZATION [dbo]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dba].[TRUNCATE_AUDIT](

[Truncate_Audit_Id] [int] IDENTITY(1,1) NOT NULL,

[HostName] [varchar](30) NOT NULL CONSTRAINT [DF__TRUNCATE___HostN__440B1D61] DEFAULT (host_name()),

[UserName] [varchar](30) NOT NULL,

[DATETIME] [datetime] NOT NULL CONSTRAINT [DF__TRUNCATE___DATET__44FF419A] DEFAULT (getdate()),

[TABLE_NAME] [varchar](100) NOT NULL,

[TABLE_OWNER] [varchar](30) NOT NULL,

[SUCCESS] [int] NOT NULL CONSTRAINT [DF__TRUNCATE___SUCCE__45F365D3] DEFAULT ((-1)),

CONSTRAINT [PK__TRUNCATE_AUDIT__4316F928] PRIMARY KEY CLUSTERED

[Truncate_Audit_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dba].[TRUNCATE_LIST](

[List_Id] [int] IDENTITY(1,1) NOT NULL,

[List_Table_Name] [varchar](256) NOT NULL,

CONSTRAINT [PK_dba.TRUNCATE_LIST] PRIMARY KEY CLUSTERED

[List_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

USE [{database}]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE Procedure [dbo].[TRUNCATE_TBL] @tbl varchar(1000),@owner varchar(30)='dbo'

WITH EXECUTE AS SELF

as

Declare @username varchar(30)

Set @username = Original_login()

If exists (select * from dba.truncate_list

Where [List_Table_Name] = @tbl)

Begin

Declare @rc int

Declare @truncate_Audit_id int

Insert into dba.truncate_Audit (table_name,table_owner,username)

Values (@tbl,@owner,@userName)

Set @truncate_audit_id = scope_identity()

declare @dynamic_sql nvarchar(1000)

set @dynamic_sql = 'Truncate Table ' + @owner + '.' + @tbl

--Print @dynamic_sql

set @rc = -1

BEGIN TRY

exec @rc = sp_executeSQL @dynamic_SQL

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000),

@ErrorNumber INT,

@ErrorSeverity INT,

@ErrorState INT,

@ErrorLine INT,

@ErrorProcedure NVARCHAR(200);

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 @RC

END CATCH

Update dba.truncate_audit

Set success = @rc

Where truncate_audit_id = @truncate_audit_id

End

ELSE

Begin

RAISERROR('Cannot find the object because it does not exist or you do not have permissions.',

16,1,1088,16,7,'[dbo].[TRUNCATE_TBL]',1)

Return -1

End