SQL OPENROWSET and user group authentication

Nancy,

This not exactly what you are asking for, but may help.  I have created two reports that query report parameters on run reports, and have created two SSRS reports - one where the reports is the parameter, the other where the user is the parameter.  Here is the first - happy to send the whole reports if interested.  It has been tweaked a bit over time, but seems to work well.  Don't know if this data is included in v16 internally or not.

BWG

USE [impresario]
GO

/****** Object: StoredProcedure [dbo].[LRP_REPORT_RUN] Script Date: 2/4/2022 1:05:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[LRP_REPORT_RUN] (@report_id varchar(100), @start_dt datetime, @end_dt datetime) as
--*/
/*
declare @report_id char(100), @start_dt datetime, @end_dt datetime
set @report_id = 'trn_by_post'
set @start_dt = '3/1/2019'
set @end_dt = '3/18/2019'
drop table #t1
drop table #t2
create table #t2 (sqlstring nvarchar(max))
drop table #t3
create table #t3 (sqlstring nvarchar(max))
--select * from #t2
--select * from #t3
--exec [LRP_REPORT_RUN] @report_id = 'trn_by_post', @start_dt = '3/1/2019', @end_dt = '3/18/2019'
*/

create table #t1 (
description varchar(500),
parameter_id int,
data_col varchar(60),
request_id int)

declare @display_col varchar(100), @parameter_id int, @request_id int, @table_name varchar(100), @data_col varchar(200), @value varchar(max), @where_clause varchar(max)

declare cur_table insensitive cursor for
select display_col, e.parameter_id, e.request_id, f.table_name, f.data_col, isnull(e.value,'NULL'), case when CHARINDEX('<<',f.where_clause) > 0 then '' else f.where_clause end
from gooesoft_request a
join gooesoft_request_parameter e on a.report_id = e.report_id and a.id = e.request_id
join gooesoft_report_parameter f on a.report_id = f.report_id and e.parameter_id = f.id and isnull(e.value,'') <> ''
where a.report_id = @report_id and a.request_date_time >= @start_dt and a.request_date_time <= @end_dt and ISNULL(f.table_name,'') <> ''
and charindex(',',e.value) = 0
order by request_id, parameter_id

open cur_table
fetch cur_table into @display_col, @parameter_id, @request_id, @table_name, @data_col, @value, @where_clause
while @@FETCH_STATUS >= 0
begin

DECLARE @sqlString nvarchar(max)
SELECT @sqlString =
'insert #t1 (description , parameter_id, data_col, request_id)
select ' + @display_col + ', ' + CAST(@parameter_id as varchar(30)) + ' as parameter_id, ' + @data_col + ' as data_col, ' + cast(@request_id as varchar(30)) +
' as request_id from ' + @table_name + ' where ' + @data_col + ' = ''' + replace(@value,'"','') + '''' + case when ISNULL(@where_clause,'') = '' then '' else ' and ' + @where_clause end
--insert #t2
--select @sqlString
EXECUTE sp_executesql @sqlString
fetch cur_table into @display_col, @parameter_id, @request_id, @table_name, @data_col, @value, @where_clause
end
close cur_table
deallocate cur_table

-----------------------

declare cur_table2 insensitive cursor for
select display_col, e.parameter_id, e.request_id, f.table_name, f.data_col, isnull(e.value,'NULL'), case when CHARINDEX('<<',f.where_clause) > 0 then '' else f.where_clause end
from gooesoft_request a
join gooesoft_request_parameter e on a.report_id = e.report_id and a.id = e.request_id
join gooesoft_report_parameter f on a.report_id = f.report_id and e.parameter_id = f.id
where a.report_id = @report_id and a.request_date_time >= @start_dt and a.request_date_time <= @end_dt and ISNULL(f.table_name,'') <> ''
and charindex(',',e.value) > 0
order by request_id, parameter_id

open cur_table2
fetch cur_table2 into @display_col, @parameter_id, @request_id, @table_name, @data_col, @value, @where_clause
while @@fetch_status >= 0
begin

declare @sqlString2 nvarchar(max)
select @sqlString2 =
'insert #t1 (description , parameter_id, data_col, request_id)
select ' + @display_col + ', ' + cast(@parameter_id as varchar(30)) + ' as parameter_id, ' + @data_col + ' as data_col, ' + cast(@request_id as varchar(30)) +
' as request_id from ' + @table_name + ' where ' + @data_col + ' in (' + replace(@value,'"','''') + ')' + case when isnull(@where_clause,'') = '' then '' else ' and ' + @where_clause end
--insert #t3
--select @sqlString2
execute sp_executesql @sqlString2
fetch cur_table2 into @display_col, @parameter_id, @request_id, @table_name, @data_col, @value, @where_clause
end
close cur_table2
deallocate cur_table2

-------------------------------------

select
c.name as report_name,
c.description as report_description,
d.description as report_category,
b.lname + ', ' + b.fname as run_by,
a.request_date_time,
isnull(cast(case when g.data_col = 'NULL' then null else g.data_col end as varchar(100)),
case when e.value = 'NULL' then null else e.value end) as parameter_value,
f.description as parameter_name,
g.description as parameter_description
from gooesoft_request a
join t_metuser b on a.user_id = b.userid
join gooesoft_report c on a.report_id = c.id
join gooesoft_report_category d on c.category = d.id
join gooesoft_request_parameter e on a.report_id = e.report_id and a.id = e.request_id
join gooesoft_report_parameter f on a.report_id = f.report_id and e.parameter_id = f.id
left join #t1 g on e.request_id = g.request_id and e.parameter_id = g.parameter_id
where a.report_id = @report_id and a.request_date_time >= @start_dt and a.request_date_time <= @end_dt

go

alter authorization on [dbo].[LRP_REPORT_RUN] to schema owner
go

grant execute on [dbo].[LRP_REPORT_RUN] to [ImpUsers] as [dbo]
go

grant execute on [dbo].[LRP_REPORT_RUN] to [tessitura_app] as [dbo]
go

reports_run_by.zip

Nancy - here is my updated code for two reports - one by user and the other by report.

To the network:  you may want to update what I previously shared.

Thank you!

BWG

Thanks so much!!

you're quite welcome  - let me know if anything needs clarification.