• Replies 5 replies
  • Subscribers 336 subscribers
  • Views 78 views
  • Users 0 members are here
Related

SQL OPENROWSET and user group authentication

Nancy Sheleheda
$organization

Apologies for a deep tech SQL question:  but has anyone used OPENROWSET to call a stored procedure into a temp table?  And if you have, have you been able to pass the authentication parameter so it's within the context of the user running it?  

We have a custom report that returns an audit of the Tessitura reports that have been run, and it only returns the raw value of the parameters passed to the request.  I'd like to update the report so it returns the formatted values, and was hoping to use AP_GET_PARAMETER_VALUES to do the heavy lifting for me by calling it from within my custom stored procedure.  Hope that makes sense... any thoughts/suggestions welcome.  Thanks!

Parents
  • $organization

    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

Reply
  • $organization

    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

Children
No Data