SQL Server - How do i know the Stored Procedure owner or creater in SQL SERVER

Asked By Madhu on 27-Sep-11 05:28 AM
How do i know the Stored Procedure owner or creater  in SQL SERVER
Suchit shah replied to Madhu on 27-Sep-11 05:55 AM
There is no way implement it if you dont' write log use ddl trigger by yourself when the SP be created.

But using  select * from  sys.procedures;
u would be able to find few things like created date, modified date
Suchit shah replied to Madhu on 27-Sep-11 05:57 AM
 In sql 2005 and I believe in 2008, by default there is a trace file running in the background. This trace file contains most of the pertinent information that is needed for a DBA to audit and troubleshoot a instance. This includes the creation, deletion and modification of objects. Below are a few queries to determine if the trace is running and how to get at the information you need.

 

Code Snippet

--check if default trace is enabled

select * from sys.configurations where configuration_id = 1568

--get the current trace rollover file

--use this path with the log.trc file in the path below.

--this will cause a file rollover to get all the data

select * from ::fn_trace_getinfo(0)

--list of events object deleted, object altered

select *

from sys.trace_events

--list of categories e.g. database, sp etc..

select *

from sys.trace_categories

SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name

FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)

inner join sys.trace_events e

on eventclass = trace_event_id

INNER JOIN sys.trace_categories AS cat

ON e.category_id = cat.category_id

where databasename = 'MyDB' and

objectname = 'MySpName' and

cat.category_id = 5 and --category objects

e.trace_event_id = 46 --object creation

Reena Jain replied to Madhu on 27-Sep-11 06:29 AM
hi,

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: 09/27/2011 ******/
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: 09/27/2011 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_DB_User] 
ON DATABASE