找出使用T-SQL来部署存储过程或功能的帐户

Question

有没有办法找出用于将存储过程或函数部署到MS SQL Server数据库的帐户？SQL Server 2008及以上的任何工作都可以。

预先感谢。

Answer 1

我不能足够回声亚伦的评论；如果您有不可信任的人，请在造成伤害之前将其锁定。但是，如果这超出了您的能力，或者您正在构建影响此类事情的案例，那么这里是一个脚本，用于检查默认跟踪的历史更改，以降低登录/主机名/应用程序。它不会告诉您确切的代码更改，但它会告诉您谁/何时。

只需将日期参数设置为您需要的。我们在一些关键的生产机器上运行了此操作以监视更改...当事件存在与我们的需求符合我们的需求时，它每小时都会通过电子邮件发送DBA团队。

DECLARE @sTracePath VARCHAR(2000), @beginDate DATETIME
SET @beginDate = '2017-05-02 16:00'
SET @sTracePath = (SELECT [Path] from sys.traces where is_default = 1)
SELECT @@SERVERNAME
        ,e.NAME 
        ,case ObjectType 
         WHEN 1 THEN 'Index'
         WHEN 2 THEN 'Database'
         WHEN 4 THEN 'CHECK Constraint'
         WHEN 5 THEN 'Default or DEFAULT Constraint'
         WHEN 6 THEN 'FOREIGN KEY Constraint'
         WHEN 7 THEN 'PRIMARY KEY Constraint'
         WHEN 8 THEN 'Stored Procedure'
         WHEN 9 THEN 'User-Defined Function (UDF)'
         WHEN 13 THEN 'Trigger'
         WHEN 14 THEN 'Inline Function'
         WHEN 15 THEN 'Table Valued UDF'
         WHEN 16 THEN 'UNIQUE Constraint'
         WHEN 17 THEN 'User Table'
         WHEN 18 THEN 'View'
         WHEN 8259 THEN 'Check Constraint'
         WHEN 8260 THEN 'Default (constraint or standalone)'
         WHEN 8262 THEN 'Foreign-key Constraint'
         WHEN 8272 THEN 'Stored Procedure'
         WHEN 8276 THEN 'Trigger on Server'
         WHEN 8277 THEN '(User-defined) Table'
         WHEN 8278 THEN 'View'
         WHEN 16724 THEN 'CLR Trigger'
         WHEN 16964 THEN 'Database'
         WHEN 17232 THEN 'CLR Stored Procedure'
         WHEN 17235 THEN 'Schema'
         WHEN 17491 THEN 'DDL Event'
         WHEN 17747 THEN 'Security Event'
         WHEN 17993 THEN 'Inline Table-valued SQL Function'
         WHEN 18000 THEN 'Partition Function'
         WHEN 18004 THEN 'Table-valued SQL Function'
         WHEN 19280 THEN 'Primary Key'
         WHEN 19538 THEN 'Role'
         WHEN 19539 THEN 'SQL Login'
         WHEN 19543 THEN 'Windows Login'
         WHEN 20038 THEN 'Scalar SQL Function'
         WHEN 20821 THEN 'Unique Constraint'
         WHEN 21076 THEN 'Transact-SQL Trigger'
         WHEN 21318 THEN 'CLR Scalar Function'
         WHEN 21321 THEN 'Inline scalar SQL Function'
         WHEN 21328 THEN 'Partition Scheme'
         WHEN 21333 THEN 'User'
         WHEN 21572 THEN 'Trigger on Database'
         WHEN 21574 THEN 'CLR Table-valued Function'
         WHEN 21577 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
         WHEN 21825 THEN 'User'
         WHEN 21827 THEN 'User'
         WHEN 21831 THEN 'User'
         WHEN 21843 THEN 'User'
         WHEN 21847 THEN 'User'
         WHEN 22601 THEN 'Index'
         WHEN 22611 THEN 'XMLSchema'
         WHEN 22868 THEN 'Type'
         ELSE 'UNKNOWN' 
         end
         ,convert(varchar(25),t.starttime,120)
         ,t.DatabaseName 
         ,objectname
         ,LoginName
         ,t.HostName
         ,t.ApplicationName
        from fn_trace_gettable( @sTracePath , 5 ) as t 
        join sys.trace_events e on e.trace_event_id = t.EventClass 
        where ObjectName is not null 
        and e.name IN ('Object:Altered', 'Object:Deleted','Object:Created')
        and objecttype in (
        1,  2,  4,  5,  6,  7,  8,  9,  13, 14, 15, 16, 17, 18, 8259,   8260,   8262,   8272,   8276,   8277,   8278,   16724,  16964,  17232,  17235,  17491,  17747,  17993,  18000,  18004,  19280,  19538,  19539,  19543,  20038,  20821,  21076,  21318,  21321,  21328,  21333,  21572,  21574,  21577,  21825,  21827,  21831,  21843,  21847,  22601,  22611,  22868
        )
        and starttime > @beginDate

您可以添加诸如and t.DatabaseName!='tempdb'或and loginname!='abc123'之类的东西隐藏噪声级别的更改或您可能不希望看到的东西。