patternMinor

Who created a procedure? (SQL Server 2008 R2 )

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

2008createdwhosqlprocedureserver

Problem

Is there a way to list who created certain procure in SQL Server 2008?

In this SO link there are some answers but from 6yrs ago.

I know it's a best pratice to write your name and date, and then start to create the procedure, but I don't see this where I work.

If there is no way to do this, can this task be made with triggers?

And a bonus question. Is this the job, of a dba? to know who created it?

Thank you very much.

Solution

Yes there is a way

The table to store the results of the trigger

USE [SOME_DATABASE]
GO

CREATE TABLE [dbo].[ddl_objects_log](
    [date] [datetime] NULL DEFAULT (getdate()),
    [login_name] [nvarchar](128) NULL,
    [nt_user_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [text] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The trigger

USE [SOME_DATABASE]
GO

/****** Object:  DdlTrigger [ddl_db_trigger]    Script Date: 22/01/2015 13:41:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddl_db_trigger]
ON DATABASE
FOR CREATE_FUNCTION,
    CREATE_PROCEDURE,
    CREATE_TABLE,
    CREATE_TRIGGER,
    CREATE_VIEW,
    ALTER_FUNCTION,
    ALTER_PROCEDURE,
    ALTER_TABLE,
    ALTER_TRIGGER,
    ALTER_VIEW,
    DROP_FUNCTION,
    DROP_PROCEDURE,
    DROP_TABLE,
    DROP_TRIGGER,
    DROP_VIEW,
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS 
BEGIN
set nocount ON
    insert into ddl_objects_log(login_name, nt_user_name, program_name, host_name, text)
    select login_name, nt_user_name, program_name, host_name, EVENTDATA() from sys.dm_exec_sessions WITH(NOLOCK) where session_id=@@SPID
set nocount OFF
END

GO

ENABLE TRIGGER [ddl_db_trigger] ON DATABASE
GO

The Query to Audit the results of the trigger

USE [SOME_DATABASE]
GO

SELECT top 10
REPLACE(CONVERT(VARCHAR(250), text.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') as modify_datetime,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/EventType)')) as event_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ServerName)')) as server_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/DatabaseName)')) as database_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectType)')) as object_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) as object_name,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/UserName)')) as user_name,
Login_name,
CONVERT(VARCHAR(MAX), text.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) as command_text
FROM [SOME_DATABASE].[dbo].[ddl_objects_log]
where CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) like '%SOME_STORED_PROCEDURE%'
order by date desc
GO

Another DBA might have a different opinion, but I consider having this information readily available as important for a DBA.

Code Snippets

USE [SOME_DATABASE]
GO

CREATE TABLE [dbo].[ddl_objects_log](
    [date] [datetime] NULL DEFAULT (getdate()),
    [login_name] [nvarchar](128) NULL,
    [nt_user_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [text] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [SOME_DATABASE]
GO

/****** Object:  DdlTrigger [ddl_db_trigger]    Script Date: 22/01/2015 13:41:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddl_db_trigger]
ON DATABASE
FOR CREATE_FUNCTION,
    CREATE_PROCEDURE,
    CREATE_TABLE,
    CREATE_TRIGGER,
    CREATE_VIEW,
    ALTER_FUNCTION,
    ALTER_PROCEDURE,
    ALTER_TABLE,
    ALTER_TRIGGER,
    ALTER_VIEW,
    DROP_FUNCTION,
    DROP_PROCEDURE,
    DROP_TABLE,
    DROP_TRIGGER,
    DROP_VIEW,
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS 
BEGIN
set nocount ON
    insert into ddl_objects_log(login_name, nt_user_name, program_name, host_name, text)
    select login_name, nt_user_name, program_name, host_name, EVENTDATA() from sys.dm_exec_sessions WITH(NOLOCK) where session_id=@@SPID
set nocount OFF
END

GO

ENABLE TRIGGER [ddl_db_trigger] ON DATABASE
GO

USE [SOME_DATABASE]
GO


SELECT top 10
REPLACE(CONVERT(VARCHAR(250), text.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') as modify_datetime,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/EventType)')) as event_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ServerName)')) as server_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/DatabaseName)')) as database_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectType)')) as object_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) as object_name,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/UserName)')) as user_name,
Login_name,
CONVERT(VARCHAR(MAX), text.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) as command_text
FROM [SOME_DATABASE].[dbo].[ddl_objects_log]
where CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) like '%SOME_STORED_PROCEDURE%'
order by date desc
GO

Context

StackExchange Database Administrators Q#109093, answer score: 7

Revisions (0)

No revisions yet.