SQL Server – Integration Services Catalog – Standard Reports – Execution Logs – Permission

Background

One of the managers in our Development group opened up a Ticket last night.

Ticket

The ticket read “Database: sql_server – Issue with table refresh to dev SQL database“.

1 – Daniel Response

As a “smart aleck”, I replied :-

Can you please use SQL Server Management Studio to troubleshoot.
I think as a senior member we gave you ample permissions to troubleshoot.
You want to look at the following:

A) SQL Server Agent
B) SQL Server SSIS Catalog
C) File System Log Folder & Files

If you get permission issue, please let us know and I will create a task and grant you additional permissions.

2 – Manager Response

Daniel,

I have looked at some of this stuff and have not been able to determine what the problem is. It could be that I just don’t know what to look at.

I will reach out the developer to see if she can help me. Please keep this ticket open in case I need to ask for some guidance/assistance from you.

Thanks,
Manager

Issue

Report

Image

report_empty.201816.0102PM.png

Observation

Let us see why she all tallies are coming up zero.

And, all no detail records.

Trouble Shooting

Integration Services Catalogs

Outline

Let use review SSISDB folder permissions for our principal.

Catalog

Catalog – SSISDB

Folder Permissions
Image

folderPermissions.20181116.1107AM.PNG

Explanation

Principal has Read and Read Objects permissions.

Though she has read objects permissions it does not seem to be sufficient.

Remediation

Grant additional permission

In MS SQL Servers v2016 and above, we can grant ssis_logreader role membership to the principal.

Krishnakumar Rukmangathan
Link

The SSIS upgrade to the SSIS 2016 can be an option here. SSIS 2016 brought a new role in the SSISDB, This new ssis_logreader database-level role that you can be used to grant permissions to access the views that contain logging output to users who aren’t administrators.

Ref: Link

Change Code

Outline

  1. SSISDB Database
    • Create new database role
    • Add principals to created database role
    • Alter Code to check role membership

Processing

Create new role

SQL

USE [SSISDB]
GO

if not exists
    (

        select *

        from   sys.database_principals tblSDP

        where  tblSDP.[name] = 'ssis_logreader'
    )
begin

    print 'Create Role - ssis_logreader ...'

    CREATE ROLE [ssis_logreader]
        authorization [dbo];

    print 'Created Role - ssis_logreader '		

end
GO

Add Members to new role

SQL
Syntax

USE [SSISDB]
GO

ALTER ROLE [role]
    ADD MEMBER [principal]
GO

Sample

USE [SSISDB]
GO

ALTER ROLE [ssis_logreader]
    ADD MEMBER [LABDC\psmith]
GO

Amend Code

Outline
  1. Amend the following views
    • Database :- SSISDB
      • Views
        • catalog.event_messages
        • catalog.executions
      • Code original
        • Database Role Membership
          • ssis_admin
            • IS_MEMBER(‘ssis_admin’)
        •  SQL Server Role Member
          • sysadmin
            • IS_SRVROLEMEMBER(‘sysadmin’)
[catalog].[event_messages]
SQL
USE [SSISDB]
GO

ALTER VIEW [catalog].[event_messages]
AS
SELECT     opmsg.[operation_message_id] as [event_message_id],
           opmsg.[operation_id],
           opmsg.[message_time],
           opmsg.[message_type],
           opmsg.[message_source_type],
           opmsg.[message],
           opmsg.[extended_info_id],
           eventmsg.[package_name],
           eventmsg.[event_name],

           message_source_name =
                      CASE
                        WHEN (opmsg.message_source_type = 10) THEN 'ISServerExec'
                        WHEN (opmsg.message_source_type = 20) THEN 'Transact-SQL stored procedure'
                        ELSE eventmsg.message_source_name
                    END,
           eventmsg.[message_source_id],
           eventmsg.[subcomponent_name],
           eventmsg.[package_path],
           eventmsg.[execution_path],
           eventmsg.[threadID],
           eventmsg.[message_code]
FROM       [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
           ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE     (

                opmsg.[operation_id] in
                (
                    SELECT [id] FROM [internal].[current_user_readable_operations]
                )

                OR (IS_MEMBER('ssis_admin') = 1)

                OR (IS_SRVROLEMEMBER('sysadmin') = 1)

               -- Added on 2018-11-16 11:47 AM
               OR (IS_MEMBER('ssis_logreader') = 1)

           )
GO

[catalog].[event_messages]
SQL

USE [SSISDB]
GO

ALTER VIEW [catalog].[executions]
AS
SELECT     execs.[execution_id],
           execs.[folder_name],
           execs.[project_name],
           execs.[package_name],
           execs.[reference_id],
           execs.[reference_type],
           execs.[environment_folder_name],
           execs.[environment_name],
           execs.[project_lsn],
           execs.[executed_as_sid],
           execs.[executed_as_name],
           execs.[use32bitruntime],
           opers.[operation_type],
           opers.[created_time],
           opers.[object_type],
           opers.[object_id],
           opers.[status],
           opers.[start_time],
           opers.[end_time],
           opers.[caller_sid],
           opers.[caller_name],
           opers.[process_id],
           opers.[stopped_by_sid],
           opers.[stopped_by_name],
           opers.[operation_guid] as [dump_id],
           opers.[server_name],
           opers.[machine_name],
           ossysinfos.[total_physical_memory_kb],
           ossysinfos.[available_physical_memory_kb],
           ossysinfos.[total_page_file_kb],
           ossysinfos.[available_page_file_kb],
           ossysinfos.[cpu_count]
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers
           ON execs.[execution_id]= opers.[operation_id]
           LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
           ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      (
                opers.[operation_id] in
                        (
                           SELECT id
                           FROM [internal].[current_user_readable_operations]
                        )

                OR (IS_MEMBER('ssis_admin') = 1)

                OR (IS_SRVROLEMEMBER('sysadmin') = 1)

               -- Added on 2018-11-16 11:51 AM
                OR (IS_MEMBER('ssis_logreader') = 1)

            )

GO

Validation

Once the changes outlined above are done, one can validate by taken upon the user of the user and try to access the view.

SQL


EXECUTE AS LOGIN = 'LAB\dog';

     select top 100 *
     from [catalog].[executions]
     order by
          1 desc

revert

Output

Output – No Access

executeAs.noAccess.20181116.1247PM.PNG

Output – Sufficient Access

executeAs.sufficientAccess.20181116.1250PM

 

Summary

Revising Microsoft’s code should not be not taken lightly.

But, I think in this case there is a bit of justification.

Those are :-

  1. There is no error message when the user tries to access the reports.
    • The report skeleton is still present
    • It is just that records are filtered out
  2. Other remediation choices such as granting membership in ssis-admin ( database role ) and sysadmin ( Sql Instance Role) are more high bar

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        ( SSIS, SSRS, SSAS, Data Access, POWER BI, PBI RS, Azure AS, SSMA, LINQ, System.Data … )

        • How a non-Admin users of SSIS 2012/2014 can view SSIS Execution Reports
          Link
  2. SQL Server Central
    • Home»SQL Server 2012»SQL 2012 – General»SSIS Execution Reports Permission
      • SSIS Execution Reports Permission
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s