10

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.

7

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.

  • Hello. Thank you for the fast answer. I will make a little test here. – Racer SQL Aug 5 '15 at 14:47
  • @craig...I'm having problems here FROM [SOME_DATABASE].[dbo].[ddl_objects_log]...Should I create the table? – Racer SQL Aug 5 '15 at 14:55
  • This is possible if there is an existing trigger that will record it. The default trace (unless you have explicitly disabled it) is already running and captures the events (unless it is rolled over and the info is gone). – Kin Aug 5 '15 at 14:55
  • @RafaelPiccinelli I added the table SQL code – Craig Efrein Aug 5 '15 at 14:57
  • Thanks @CraigEfrein...@kin, I have "default trace enabled =1". I Didn't understand your comment. This trigger will only work if I already have another trigger? Sorry i'm a new in the security world. – Racer SQL Aug 5 '15 at 15:01
3

If you have default trace enabled and it has not rolled over, then you can use it to find who created the SP

/*
    Object Altered
    Object Created
    Object Deleted 
*/

SELECT  TE.name ,

        v.subclass_name ,

        DB_NAME(t.DatabaseId) AS DBName ,

        T.NTDomainName ,

        t.NTUserName ,

        t.HostName ,

        t.ApplicationName ,

        t.LoginName ,

        t.Duration ,

        t.StartTime ,

        t.ObjectName ,

        CASE t.ObjectType

          WHEN 8259 THEN 'Check Constraint'

          WHEN 8260 THEN 'Default (constraint or standalone)'

          WHEN 8262 THEN 'Foreign-key Constraint'

          WHEN 8272 THEN 'Stored Procedure'

          WHEN 8274 THEN 'Rule'

          WHEN 8275 THEN 'System Table'

          WHEN 8276 THEN 'Trigger on Server'

          WHEN 8277 THEN '(User-defined) Table'

          WHEN 8278 THEN 'View'

          WHEN 8280 THEN 'Extended Stored Procedure'

          WHEN 16724 THEN 'CLR Trigger'

          WHEN 16964 THEN 'Database'

          WHEN 16975 THEN 'Object'

          WHEN 17222 THEN 'FullText Catalog'

          WHEN 17232 THEN 'CLR Stored Procedure'

          WHEN 17235 THEN 'Schema'

          WHEN 17475 THEN 'Credential'

          WHEN 17491 THEN 'DDL Event'

          WHEN 17741 THEN 'Management Event'

          WHEN 17747 THEN 'Security Event'

          WHEN 17749 THEN 'User Event'

          WHEN 17985 THEN 'CLR Aggregate Function'

          WHEN 17993 THEN 'Inline Table-valued SQL Function'

          WHEN 18000 THEN 'Partition Function'

          WHEN 18002 THEN 'Replication Filter Procedure'

          WHEN 18004 THEN 'Table-valued SQL Function'

          WHEN 18259 THEN 'Server Role'

          WHEN 18263 THEN 'Microsoft Windows Group'

          WHEN 19265 THEN 'Asymmetric Key'

          WHEN 19277 THEN 'Master Key'

          WHEN 19280 THEN 'Primary Key'

          WHEN 19283 THEN 'ObfusKey'

          WHEN 19521 THEN 'Asymmetric Key Login'

          WHEN 19523 THEN 'Certificate Login'

          WHEN 19538 THEN 'Role'

          WHEN 19539 THEN 'SQL Login'

          WHEN 19543 THEN 'Windows Login'

          WHEN 20034 THEN 'Remote Service Binding'

          WHEN 20036 THEN 'Event Notification on Database'

          WHEN 20037 THEN 'Event Notification'

          WHEN 20038 THEN 'Scalar SQL Function'

          WHEN 20047 THEN 'Event Notification on Object'

          WHEN 20051 THEN 'Synonym'

          WHEN 20549 THEN 'End Point'

          WHEN 20801 THEN 'Adhoc Queries which may be cached'

          WHEN 20816 THEN 'Prepared Queries which may be cached'

          WHEN 20819 THEN 'Service Broker Service Queue'

          WHEN 20821 THEN 'Unique Constraint'

          WHEN 21057 THEN 'Application Role'

          WHEN 21059 THEN 'Certificate'

          WHEN 21075 THEN 'Server'

          WHEN 21076 THEN 'Transact-SQL Trigger'

          WHEN 21313 THEN 'Assembly'

          WHEN 21318 THEN 'CLR Scalar Function'

          WHEN 21321 THEN 'Inline scalar SQL Function'

          WHEN 21328 THEN 'Partition Scheme'

          WHEN 21333 THEN 'User'

          WHEN 21571 THEN 'Service Broker Service Contract'

          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 21581 THEN 'Service Broker Message Type'

          WHEN 21586 THEN 'Service Broker Route'

          WHEN 21587 THEN 'Statistics'

          WHEN 21825 THEN 'User'

          WHEN 21827 THEN 'User'

          WHEN 21831 THEN 'User'

          WHEN 21843 THEN 'User'

          WHEN 21847 THEN 'User'

          WHEN 22099 THEN 'Service Broker Service'

          WHEN 22601 THEN 'Index'

          WHEN 22604 THEN 'Certificate Login'

          WHEN 22611 THEN 'XMLSchema'

          WHEN 22868 THEN 'Type'

          ELSE 'Hmmm???'

        END AS ObjectType

FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                            value

                                                    FROM    [fn_trace_getinfo](NULL)

                                                    WHERE   [property] = 2

                                                  )), DEFAULT) T

        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id

                                            AND v.subclass_value = t.EventSubClass

WHERE   TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )

                -- filter statistics created by SQL server                                         

        AND t.ObjectType NOT IN ( 21587 )

                -- filter tempdb objects

        AND DatabaseID <> 2

                -- get only events in the past 24 hours

        AND StartTime > DATEADD(HH, -24, GETDATE())

ORDER BY t.StartTime DESC ;

Click here to enlarge

enter image description here

Is this the job, of a dba? to know who created what inside the sql?

It depends on why you want to know if objects are created/altered or dropped. You can use event notification to log and notify you if you feel that objects are created/dropped or altered by an unauthorized user. Make sure you have proper filters inplace.

enter image description here

For completeness of this answer, I want to mention - What event information can I get by default from SQL Server? from Aaron Bertrand.

  • hello @kin. I'm having problem in the "convert".But what's wrong there? It says sintax error. – Racer SQL Aug 5 '15 at 14:59
  • @RafaelPiccinelli what line you are getting an error ? I tried it and do not get any error. You also need to modify DATEADD(HH, -24, GETDATE()) if you want to get results for more than 24 hrs. – Kin Aug 5 '15 at 15:05
  • sorry, I don't know whats was wrong. I just copy/past again and its working. If I comment this line, can I get all procedures of a certain database, using ` AND DatabaseID =224` for example? – Racer SQL Aug 5 '15 at 15:13
  • @RafaelPiccinelli Glad that it is working. If you comment out that line (which is a filter) then you will get all results. Just play around with filters to get your desired results - filter by dbid/dbname or even SP name. This wont give you the user who created the SP, but will give you time when an SP was created - select * from sys.procedures where type = 'P' order by create_date desc – Kin Aug 5 '15 at 15:17
  • Thanks @Kin I'm using it right now. I really don't want to be annoying, but why am I getting only the results of 1 database? I'm using the query posted here ( the big one ) but I see only one database. If I use AND DatabaseID= 'the_Database_I_Want, It shows me nothing, even with DATEADD(HH, -24, GETDATE()) commented. Am I doing something wrong? – Racer SQL Aug 5 '15 at 15:26
3

I'm coming in late, but I "do" security and *administration things.

Is this the job, of a dba? to know who created what inside the sql?

YES. It is important to have or create these logs wherever possible. However, it's also your responsibility, in my view, to not "open this box" until it becomes important. In other words--again, in my view--it is your job to provide the repo for data, make sure it's secure, make sure it's tuned ... and then stay away from the data inside unless you're called to specifically look at it or unless it's required to accomplish your goal.

My opinion, and my view, but I have dealt with sensitive & confidential data for many years and have made it a point to very nearly never[1] "open the box," as this degrades trust with users.

Now let me take a different path: what if no one can quickly figure out how the procedure responds to edge-cases? What could take you or I an hour to puzzle over may take the author a few minutes: "ah, right, this thing fails when..."

  1. There was one and only one exception. In 2007 or so, I noticed a ton of network activity on the segment. I let it go for a day and then investigated, as it came from a person's machine who seemed to be out of the office more than in. On the day in question, she was gone for half of the day, so my hand was forced. It turns out she was downloading CDs from Limewire and Bearshare for her upcoming wedding. I turned it over to her boss for discussion. He decided to do nothing, but I thought he should at least know, as it put the organization at risk of legal action.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.