Integration Services目录文件夹权限已更改 [英] Integration Services Catalog folder permissions changed

问题描述

问题：SSISDB或MSDB中的任何SQL Server系统表是否包含使我能够发现哪些用户正在更改Integration Services目录中的文件夹权限的信息？

背景：我看到一个SQL Agent Job失败，并显示以下错误描述：

无法访问该程序包或包不存在。验证该软件包是否存在
，并且用户具有对该软件包的权限。

研究此问题后，我发现服务帐户对包含相关软件包的文件夹的权限已删除。我想执行诊断以查看谁删除了权限以及何时删除了权限。

此外，由于SQL Server尚未提供服务，因此IT运营小组的配置管理仍然是一个挑战。至少在以下方面跨环境（即，开发，测试，阶段和生产）保持在线：

1. 补丁管理


2. 内存管理


3. 权限管理

研究：我已经检查了MSDB中的Sysssispackages表，但这似乎没有帮助，SSISDB中的表似乎都是基于整数的。我的背景是数据库开发，而不是数据库管理。感谢您的帮助。

解决方案

SSISDB的权限更改未经审计。

使用UI授予/拒绝文件夹/项目的权限时，即转换为对 catalog.grant_permission / <$的调用c $ c> catalog.deny_permissions 那些检查您是否是管理员角色（服务器或数据库），如果是，则调用 internal.update_permission 授予与拒绝的值为0/1。

与2014年相比，我进行了测试，但如果2016/2017/2019年有所不同，我会感到惊讶/ p>

SQL Server本身通过系统跟踪跟踪权限更改。假设更改是最近的，则可以尝试这样的查询

  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 
系统跟踪t 
交叉应用sys.fn_trace_gettable（REVERSE（SUBSTRING（REVERSE（t.path），CHARINDEX（'\'，REVERSE（t.path）），260） ）+ N'log.trc'，默认）f 
 WHERE 
 t.is_default = 1 
 AND f.EventClass IN 
（102，103，104，105，106， 108、109、110、111）
和f.DatabaseName ='SSISDB'; 
  

事件类突破在
> https://www.databasejournal.com/features/ mssql / a-few-cool-things-you-can-identify-using-the-default-trace.html

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.

解决方案

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

这篇关于Integration Services目录文件夹权限已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！