If a user has a proc which needs to do for example a truncate within then the user needs ot have elevated rights, typically dbo owner, to work, if thi si snot possible then you need to use a wrapper stored proc to temporarily elevate the user’s access while running the proc.

The steps are as follows:

– create role sii_role with passwd ‘********’

– grant role sa_role to sii_role

– grant role sii_role to — this gets added as inactive by default so needs to be set to on, i.e its safe

– Create the wrapper stored proc, e.g:
create procedure iSP_bcp_ts_gross_trade_audit_wrap
(
@replay_mode char(1) = NULL –The same variables as in the stored proc being called
)
as
begin
/*
This proc calls SP_bcp_ts_gross_trade_audit.
Call order is:
iSP_bcp_ts_gross_trade_audit_wrap
iSP_bcp_ts_gross_trade_audit
*/

set nocount on

— enable sa_role, DBA’s to replace BLAH with real password and run sp_hidetext
set role sii_role with passwd “********” on
–We have sa_role so run original code
exec iSP_bcp_ts_gross_trade_audit @replay_mode
— disable sa_role
set role sii_role off

return 0
end
GO

– sp_hidetext iSP_bcp_ts_gross_trade_audit_wrap

– grant execute on iSP_bcp_ts_gross_trade_audit_wrap to

– The user can then execute this new wrapper stored proc as they did the old stored proc and it should work fine.