I have a stored procedure in a database OmegaCoreAudit that queries the master.sys.fn_get_audit_file records and stores them to a table in the same database.
The owner of this database is login OMEGACA, which is SYSADMIN and also mapped to the database with same (OMEGACA) DB user and granted public role.
Every object in this database is created under schema OMEGACA, which is owned by database user OMEGACA.
This procedure is named P_SS_AUD_UNF_PULL, and it contains:
P_SS_AUD_UNF_TRAIL- procedure that inserts the current record in loop to my tableP_SS_ERROR_LOG- procedure inserts any eventual error into another error_log tablesys_aud- table that stores the last processedevent_time
The procedure code is like this:
ALTER PROCEDURE [OMEGACA].[P_SS_AUD_UNF_PULL]
-- WITH EXECUTE AS 'OMEGACA'
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @v_tst_last Datetime2;
DECLARE @v_max_rows int;
DECLARE @v_aud_path nvarchar(4000);
DECLARE @v_tst_new Datetime2;
-- get sys_aud data
SELECT
@v_tst_last = TIMESTAMP_LST_AUD,
@v_max_rows = max_rows, @v_aud_path = aud_path
FROM
OMEGACA.sys_aud
WHERE
sys_aud_id = 1;
DECLARE @v_Error_data nvarchar(4000);
-- Update UTC last
IF @v_tst_last IS NULL
BEGIN
BEGIN TRANSACTION;
SET @v_tst_last = SYSUTCDATETIME();
UPDATE OMEGACA.sys_aud
SET timestamp_lst_aud = @v_tst_last
WHERE sys_aud_id = 1;
COMMIT TRANSACTION;
END;
declare Cur_Aud CURSOR LOCAL READ_ONLY FORWARD_ONLY for
select
event_time, sequence_number, action_id, succeeded, permission_bitmask, is_column_permission, session_id,
server_principal_id, database_principal_id, target_server_principal_id, target_database_principal_id,
object_id, class_type, session_server_principal_name, server_principal_name, server_principal_sid,
database_principal_name, target_server_principal_name, target_server_principal_sid, target_database_principal_name,
server_instance_name, database_name, schema_name, object_name, statement, additional_information, file_name,
audit_file_offset, user_defined_event_id, user_defined_information, audit_schema_version, sequence_group_id,
transaction_id, client_ip, application_name, duration_milliseconds, response_rows, affected_rows, connection_id,
data_sensitivity_information, host_name
from master.sys.fn_get_audit_file(
@v_aud_path,
DEFAULT,
DEFAULT
)
where event_time > @v_tst_last
order by event_time asc;
declare @v_TIMESTAMP_STS datetime2(7);
declare @v_cur_event_time datetime2(7);
declare @v_cur_sequence_number int;
declare @v_cur_action_id varchar(4);
declare @v_cur_succeeded bit;
declare @v_cur_permission_bitmask varbinary(16);
declare @v_cur_is_column_permission bit;
declare @v_cur_session_id smallint;
declare @v_cur_server_principal_id int;
declare @v_cur_database_principal_id int;
declare @v_cur_target_server_principal_id int;
declare @v_cur_target_database_principal_id int;
declare @v_cur_object_id int;
declare @v_cur_class_type varchar(2);
declare @v_cur_session_server_principal_name nvarchar(128);
declare @v_cur_server_principal_name nvarchar(128);
declare @v_cur_server_principal_sid varbinary(85);
declare @v_cur_database_principal_name nvarchar(128);
declare @v_cur_target_server_principal_name nvarchar(128);
declare @v_cur_target_server_principal_sid varbinary(85);
declare @v_cur_target_database_principal_name nvarchar(128);
declare @v_cur_server_instance_name nvarchar(128);
declare @v_cur_database_name nvarchar(128);
declare @v_cur_schema_name nvarchar(128);
declare @v_cur_object_name nvarchar(128);
declare @v_cur_statement nvarchar(4000);
declare @v_cur_additional_information nvarchar(4000);
declare @v_cur_file_name nvarchar(260);
declare @v_cur_audit_file_offset bigint;
declare @v_cur_user_defined_event_id smallint;
declare @v_cur_user_defined_information nvarchar(4000);
declare @v_cur_audit_schema_version int;
declare @v_cur_sequence_group_id varbinary(85);
declare @v_cur_transaction_id bigint;
declare @v_cur_client_ip nvarchar(128);
declare @v_cur_application_name nvarchar(128);
declare @v_cur_duration_milliseconds bigint;
declare @v_cur_response_rows bigint;
declare @v_cur_affected_rows bigint;
declare @v_cur_connection_id uniqueidentifier;
declare @v_cur_data_sensitivity_information nvarchar(4000);
declare @v_cur_host_name nvarchar(128);
BEGIN TRANSACTION
-- BEGIN LOOP C_AUD
OPEN Cur_Aud;
fetch next from Cur_Aud
into @v_cur_event_time, @v_cur_sequence_number, @v_cur_action_id, @v_cur_succeeded, @v_cur_permission_bitmask, @v_cur_is_column_permission, @v_cur_session_id,
@v_cur_server_principal_id, @v_cur_database_principal_id, @v_cur_target_server_principal_id, @v_cur_target_database_principal_id,
@v_cur_object_id, @v_cur_class_type, @v_cur_session_server_principal_name, @v_cur_server_principal_name, @v_cur_server_principal_sid,
@v_cur_database_principal_name, @v_cur_target_server_principal_name, @v_cur_target_server_principal_sid, @v_cur_target_database_principal_name,
@v_cur_server_instance_name, @v_cur_database_name, @v_cur_schema_name, @v_cur_object_name, @v_cur_statement, @v_cur_additional_information, @v_cur_file_name,
@v_cur_audit_file_offset, @v_cur_user_defined_event_id, @v_cur_user_defined_information, @v_cur_audit_schema_version, @v_cur_sequence_group_id,
@v_cur_transaction_id, @v_cur_client_ip, @v_cur_application_name, @v_cur_duration_milliseconds, @v_cur_response_rows, @v_cur_affected_rows, @v_cur_connection_id,
@v_cur_data_sensitivity_information, @v_cur_host_name;
while @@FETCH_STATUS = 0
BEGIN
-- Make Local SysTimeStamp;
set @v_TIMESTAMP_STS =
CONVERT(
datetime2,
SWITCHOFFSET(
CONVERT(datetimeoffset, @v_cur_event_time), DATENAME(TzOffset, SYSDATETIMEOFFSET())
)
);
-- Insert UNF Trail for AUD;
EXECUTE OMEGACA.P_SS_AUD_UNF_TRAIL
@v_cur_event_time,
@v_cur_sequence_number,
@v_cur_action_id,
@v_cur_succeeded,
@v_cur_permission_bitmask,
@v_cur_is_column_permission,
@v_cur_session_id,
@v_cur_server_principal_id,
@v_cur_database_principal_id,
@v_cur_target_server_principal_id,
@v_cur_target_database_principal_id,
@v_cur_object_id,
@v_cur_class_type,
@v_cur_session_server_principal_name,
@v_cur_server_principal_name,
@v_cur_server_principal_sid,
@v_cur_database_principal_name,
@v_cur_target_server_principal_name,
@v_cur_target_server_principal_sid,
@v_cur_target_database_principal_name,
@v_cur_server_instance_name,
@v_cur_database_name,
@v_cur_schema_name,
@v_cur_object_name,
@v_cur_statement,
@v_cur_additional_information,
@v_cur_file_name,
@v_cur_audit_file_offset,
@v_cur_user_defined_event_id,
@v_cur_user_defined_information,
@v_cur_audit_schema_version,
@v_cur_sequence_group_id,
@v_cur_transaction_id,
@v_cur_client_ip,
@v_cur_application_name,
@v_cur_duration_milliseconds,
@v_cur_response_rows,
@v_cur_affected_rows,
@v_cur_connection_id,
@v_cur_data_sensitivity_information,
@v_cur_host_name ;
-- Mark TST
if @v_tst_new is NULL
begin
set @v_tst_new = @v_cur_event_time; -- first rec
end
else -- get Latest
begin
if @v_cur_event_time > @v_tst_new
begin
set @v_tst_new = @v_cur_event_time;
end;
end;
fetch next from Cur_Aud
into @v_cur_event_time, @v_cur_sequence_number, @v_cur_action_id, @v_cur_succeeded, @v_cur_permission_bitmask, @v_cur_is_column_permission, @v_cur_session_id,
@v_cur_server_principal_id, @v_cur_database_principal_id, @v_cur_target_server_principal_id, @v_cur_target_database_principal_id,
@v_cur_object_id, @v_cur_class_type, @v_cur_session_server_principal_name, @v_cur_server_principal_name, @v_cur_server_principal_sid,
@v_cur_database_principal_name, @v_cur_target_server_principal_name, @v_cur_target_server_principal_sid, @v_cur_target_database_principal_name,
@v_cur_server_instance_name, @v_cur_database_name, @v_cur_schema_name, @v_cur_object_name, @v_cur_statement, @v_cur_additional_information, @v_cur_file_name,
@v_cur_audit_file_offset, @v_cur_user_defined_event_id, @v_cur_user_defined_information, @v_cur_audit_schema_version, @v_cur_sequence_group_id,
@v_cur_transaction_id, @v_cur_client_ip, @v_cur_application_name, @v_cur_duration_milliseconds, @v_cur_response_rows, @v_cur_affected_rows, @v_cur_connection_id,
@v_cur_data_sensitivity_information, @v_cur_host_name;
END;
CLOSE Cur_Aud;
DEALLOCATE Cur_Aud;
-- END LOOP C_AUD
COMMIT TRANSACTION
-- Update New TST
if @v_tst_new is not null
begin
BEGIN TRANSACTION;
update OMEGACA.sys_aud set
timestamp_lst_aud = @v_tst_new
where sys_aud_id = 1;
COMMIT TRANSACTION;
end;
END TRY
BEGIN CATCH
--ROLLBACK TRANSACTION;
-- Update New TST
if @v_tst_new is not null
begin
BEGIN TRANSACTION;
update OMEGACA.sys_aud set
timestamp_lst_aud = @v_tst_new
where sys_aud_id = 1;
COMMIT TRANSACTION;
end;
set @v_Error_data = 'Proc: ' + ERROR_PROCEDURE()
+ CHAR(13)
+ 'Line: ' + CAST(ERROR_LINE() as Varchar(20))
+ CHAR(13)
+ 'Error: ' + ERROR_MESSAGE();
EXEC [OMEGACA].[P_SS_ERROR_LOG] @v_cur_client_ip, @v_Error_data ;
END CATCH
END;
I connect with login OMEGACA, and exec the procedure as it is above. Everything goes OK, if in the MS SQL audit files there are new records, they will be inserted to my table.
Now at the top of the procedure I do uncomment the "EXECUTE AS 'OMEGACA'" line.
Still logged on as OMEGACA, I exec the procedure: this time new records are not inserted to my table, and into my error_log table I do see:
Proc: OMEGACA.P_SS_AUD_UNF_PULL
Line: 43
Error: The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.
Question:
- Why do I get this error?
(I am already executing the procedure asOMEGACA, so what would change if I add a hardcode "WITH EXECUTE as ... " to the procedure?) - How can I overcome this keeping the
WITH EXECUTE as ...
PS: in practice, I do call this procedure from a job, that is owned by principal OMEGACA. Same behavior with that kind of calling too.
