2015年10月29日 星期四

建立TRUNCATE TABLE的Stored procedure


--建立Stored procedure
CREATE PROCEDURE [dbo].[mysp_truncate]  @tableName varchar(50)
with execute as owner
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL='TRUNCATE TABLE dbo.' + @tableName
EXEC (@SQL);
GO
--授予執行權限
--這樣就算使用者沒有ALTER權限也可以TRUNCATE TABLE
--ldf檔才不會一直增加(寫入transaction log)
grant execute on [dbo].[mysp_truncate] to 資料庫使用者;
GO
--執行Stored procedure
exec mysp_truncate 表格名稱;

出處:
Creating a Stored Procedure to Truncate a Table


TRUNCATE TABLE (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms177570.aspx
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.


ref:
EXECUTE AS 子句 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms188354(v=sql.120).aspx

沒有留言:

張貼留言