最小限の権限でtruncateを実行する方法
db_owner もしくは db_ddladmin 権限が必要。
回避するには以下のspを利用する。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pTruncate]
@TblName varchar(50)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
DECLARE @strSQL varchar(500)
DECLARE @object_id int
SET @object_id = OBJECT_ID(@TableName)
BEGIN TRY
IF @object_id IS NOT NULL
BEGIN
BEGIN TRANSACTION;
SET @strSQL = 'TRUNCATE TABLE [' + @TableName + '];'
EXECUTE (@strSQL)
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT N'Table name : ' + @TableName + ' No existe'
END
END TRY
BEGIN CATCH
PRINT N'error'
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
END CATCH