Integration Services Catalog folder permissions ch

Question

Question: Do any of the SQL Server systems tables in either SSISDB or MSDB contain information that would allow me to discover which users are making changes to folder permissions in the Integration Services Catalog?

Background: I saw that a SQL Agent Job was failing with the following error description:

Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

Upon researching the issue, I found that the service account's permissions to the folder that contains the relevant packages has been removed. I would like to perform a diagnostic to see who removed the permissions and when.

Additionally, configuration management by our IT Operations Group has been a challenge as the SQL Servers have not been kept in-line across environments (i.e., development, test, stage, and production) with respect to at least the following:

1. Patch Management
2. Memory Management
3. Permissions Management

Research: I have checked the Sysssispackages table in MSDB but that does not appear helpful and the tables in SSISDB all appear to be integer based. My background is in database development and less so in database management. Any help is appreciated.

Answer 1

The changes to permissions are not audited for the SSISDB.

When you're using the UI to grant/deny permissions for a folder/project, that is translated to a call to catalog.grant_permission/catalog.deny_permissions Those check whether you're in an admin role (server or database) and if so, then call the internal.update_permission with a value of 0/1 for grant vs deny.

I tested this versus 2014 but I would be surprised if it's any different in 2016/2017/2019

SQL Server itself keep track of permission changes via the system trace. Assuming the change was recent, you can try a query like this

SELECT
    f.ObjectName
,   f.NTUserName
,   f.StartTime AS ChangeStartTime
,   f.EventClass
,   t.start_time AS TraceStartTime
,   t.last_event_time AS TraceLastEventTime
,   t.event_count
,   f.DatabaseID
,   f.TransactionID
--,   f.NTDomainName
,   f.HostName
,   f.ClientProcessID
--,   f.ApplicationName
,   f.LoginName
,   f.SPID
,   f.EventSubClass
,   f.ObjectID
,   f.ObjectType
,   f.DatabaseName
FROM
    sys.traces t
    CROSS APPLY sys.fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path), CHARINDEX('\', REVERSE(t.path)), 260)) + N'log.trc', DEFAULT) f
WHERE
    t.is_default = 1
    AND f.EventClass IN
    (102, 103, 104, 105, 106, 108, 109, 110, 111)
    AND f.DatabaseName = 'SSISDB';

Event class breakout is at https://www.databasejournal.com/features/mssql/a-few-cool-things-you-can-identify-using-the-default-trace.html

Answer 2

I don't think that SSISDB either MSDB contains informations about permissions changes auditing. If think this should be done on the SQL Server database engine. You should have an auditing process that monitor all changes on the SQL Server Instance.

You can refer to the following links to read more on how you can track or audit permissions changes:

• Tracking SQL Server Database Permission Changes
• SQL Server Audit
• Audit SQL Server permission changes to improve overall security

To check out what are the tables and informations stored inside MSDB and SSISDB check the following links:

• SSIS Catalog
• List of tables in SSISDB
• The system msdb database, introduction and tips
• msdb Database