Microsoft SQL Server - 谁创建了存储过程？

Question

有没有一种好方法可以告诉谁在 SQL Server 2005 中创建了存储过程（在 2008 中也适用）？ 在 SQL Management Studio 中，我可以在过程上右键单击/属性来获取创建日期/时间，但如何发现创建者？

Answer 1

现在对您来说可能为时已晚，但您可以跟踪 DDL 活动。

我们的管理数据库中有一个表，其中包含放入其中的所有活动。 它使用 2005 年新增的 DDL 触发器。这些脚本在您的管理数据库（对我来说是 SQL_DBA）中创建一个表，在模型数据库上创建一个触发器，在现有数据库上创建触发器。 我还在最后创建了一个 sp_msforeachDB 语句来禁用所有这些语句。

一个警告 - 您的数据库需要处于 90 的兼容模式（在每个数据库的选项中），否则您可能会开始出现错误。 语句的 EXECUTE AS 部分中的帐户也需要访问权限才能插入到您的管理表中。

USE [SQL_DBA]
GO
/****** Object:  Table [dbo].[DDL_Login_Log]    Script Date: 03/03/2009 17:28:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDL_Login_Log](
    [DDL_Id] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DB_User] [nvarchar](100) NULL,
    [DBName] [nvarchar](100) NULL,
    [Event] [nvarchar](100) NULL,
    [TSQL] [nvarchar](2000) NULL,
    [Object] [nvarchar](1000) NULL,
 CONSTRAINT [PK_DDL_Login_Log] PRIMARY KEY CLUSTERED 
(
    [DDL_Id] ASC,
    [PostTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--This creates the trigger on the model database so all new DBs get it
USE [model]
GO
/****** Object:  DdlTrigger [ddl_DB_User]    Script Date: 03/03/2009 17:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_DB_User] 
ON DATABASE
FOR DDL_DATABASE_SECURITY_EVENTS
AS 

DECLARE @data XML
declare @user nvarchar(100)

SET @data = EVENTDATA()
select @user = convert(nvarchar(100), SYSTEM_USER)

execute as login='domain\sqlagent'
INSERT sql_dba.dbo.DDL_Login_Log 
   (PostTime, DB_User, DBName, Event, TSQL,Object) 
   VALUES 
   (@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)'), 
   @user,
    db_name(),
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(1000)')
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--CREATE TRIGGER IN ALL NON SYSTEM DATABASES

DECLARE @dataname varchar(255),
@dataname_header varchar(255),
@command VARCHAR(MAX),
@usecommand VARCHAR(100)
SET @command = '';
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases 
WHERE name not in ('master', 'pubs', 'tempdb', 'model','msdb')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status = 0)
BEGIN

PRINT '----------BEGIN---------'

PRINT 'DATANAME variable: ' + @dataname;

EXEC ('USE ' + @dataname);

PRINT 'CURRENT db: ' + db_name();

SELECT @command = 'CREATE TRIGGER DBA_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'', ''NVARCHAR(1000)'')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''','''')))
SET @posttime = @data.value(''(/EVENT_INSTANCE/PostTime)[1]'', ''DATETIME'')
SET @spid = @data.value(''(/EVENT_INSTANCE/SPID)[1]'', ''nvarchar(6)'')
SET @loginname = @data.value(''(/EVENT_INSTANCE/LoginName)[1]'',
    ''NVARCHAR(100)'')
SET @hostname = HOST_NAME()
INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName)
 VALUES(@cmd, @posttime, @hostname, @loginname);'

 EXEC (@command);
 FETCH NEXT FROM datanames_cursor INTO @dataname;
PRINT '----------END---------'
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

----Disable all triggers when things go haywire
sp_msforeachdb @command1='use [?]; IF  EXISTS (SELECT * FROM sys.triggers WHERE name = N''ddl_DB_User'' AND parent_class=0)disable TRIGGER [ddl_DB_User] ON DATABASE'

Answer 2

如果它不是很久以前创建的，请尝试以下操作：

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)

它选择当前（开箱即用的）默认跟踪。 如果它是最近创建的（并且服务器最近没有重新启动），那么存储过程对象名称和创建它的登录名将在跟踪数据中。

Answer 3

我相信这在 SQL 2005 中不可用。当然，它在 SQL Management Studio 的属性中不可用，在 sys.objects 表或我能看到的任何其他表中也不可用。

Answer 4

按照与 Sam 相同的想法，您可以使用 DDL 触发器来捕获所需的信息，然后将该数据发送到 SQL 服务代理队列，该队列可以将其转发到管理数据库（如果需要，可以位于另一台服务器上），这将然后保存所有 DDL 更改。

这将消除权限问题，因为 DDL 触发器会将数据加载到本地数据库的 Service Broker 队列中，并且 SQL 处理将消息移动到其他数据库的操作。

使用此方法需要进行更多设置，但是一旦设置，无论是谁更改了对象，它都会起作用。

Answer 5

如何事后（尤其是几年后）获取这条信息很可能是不可能的。

但是，您可以使用 SQL Server Profiler 来跟踪 DDL 操作。 在事件选择中，检查以下事件：

对象/对象：已更改的

对象/对象：已创建的

对象/对象：已删除

还有许多自定义选项：您可以将输出保存到文件或表，还可以根据任何内容进一步过滤输出列等