Microsoft SQL Server - Who created a Stored Proced

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?

回答1:

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

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.

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'

回答2:

I believe this is not available in SQL 2005. Certainly it's not available in the properties in SQL Management Studio, and not available in the sys.objects table or any others I can see.

回答3:

If it was not created too long ago, try this:

DECLARE @path varchar(256)

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

SELECT *
FROM fn_trace_gettable(@path, 1)

It selects the current (out of the box) default trace. If it was created recently (and the server hasn't been restarted recently), then the stored procedure object name and the login name that created it will be in the trace data.

回答4:

Along the same idea as Sam's, you could use a DDL trigger to capture the needed information, then send that data to a SQL Service broker queue, which could forward it to the Admin database (which could be on another server if needed) which would then hold all the DDL changes.

This would remove the permissions issue as the DDL trigger would be loading data into a Service Broker Queue at the local database and SQL handles the moving of the message to the other database.

There would be a bit more setup with this method, but once setup it would work no matter who made the object change.

回答5:

How to get this piece of info ex post (especially years later) is most likely not possible.

However, you can use SQL Server Profiler to track DDL actions. In Event Selection, check the following events:

Objects / Object: Altered

Objects / Object: Created

Objects / Object: Deleted

There are also lots of customization options: you can save the output to a file or table, filter the output furthermore based on any columns etc. etc.