Microsoft SQL Server-谁创建了存储过程? [英] Microsoft SQL Server - Who created a Stored Procedure?

问题描述

是否有一种很好的方法来告诉谁在SQL Server 2005中创建了存储过程(在2008年也可以使用)?在SQL Management Studio中，我可以在proc上右键单击鼠标/属性以获取创建的日期/时间，但是如何找到创建者?

Is there a good way to tell who created a stored procedure in SQL Server 2005 (that also works in 2008)? In SQL Management Studio I can right mouse/properties on a proc to get the created date/time but how do I discover the creator?

推荐答案

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

It may be too late for you now, but you can keep track of DDL activity.

我们的管理数据库中有一个表，该表中包含所有活动.它使用DDL触发器，这是2005年的新功能.这些脚本在您的管理数据库(对我来说是SQL_DBA)中创建一个表，在模型db上创建一个触发器，在现有数据库上创建一个触发器.我还在末尾创建了一个sp_msforeachDB语句以禁用所有它们.

We have a table in our administrative database that gets all the activity put in it. It uses a DDL trigger, new to 2005. These scripts create a table in your admin DB (SQL_DBA for me), create a trigger on the model db, create triggers on existing databases. I also created a sp_msforeachDB statement at the end to disable all of them.

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

One caveat - your databases need to be in compatibility mode of 90(in options for each db), otherwise you may start getting errors. The account in the EXECUTE AS part of the statement also needs access to insert into your admin table.

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'

这篇关于Microsoft SQL Server-谁创建了存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！