SQL Server database, execute statements across permissions

In the original: SQL Server database, execute statements across permissions

Problem Source: Recently a colleague needs to execute a bulk DELETE statement. Based on his business needs, he is recommended to use the "TRUNCATE TABLE" statement. However, using this statement requires ALTER permission, which does not match the role of the executing user.

WORKAROUND: Use the EXECUTE AS statement to modify execution permissions. The code is as follows:

ALTER PROCEDURE [dbo].[sp_truncatetable]     @TableName varchar( $)      with EXECUTE  as Self as BEGIN     --print out the current execution context user    SELECTNt_username, Loginame fromsys.sysprocessesWHEREspid= @ @SPID    SETNOCOUNT on; DECLARE @TruncateSql nvarchar( -); SET @TruncateSql = 'TRUNCATE TABLE' + @TableName     EXEC(@TruncateSql) END

By printing out the loginame can be seen that the execution of user information has been modified.

SQL Server database, execute statements across permissions