Created Stored Procedure

Question

Created Stored Procedure

Lester Policarpio

SSCertifiable

Points: 7109
More actions
February 27, 2008 at 11:39 pm

#71332

Gud Day to all
I made a script to monitor all db,tables,sp,views,triggers that are created in our production server daily so that i will be updated for what ever is created in our production servers. My script runs at exactly 9:30 AM and sends a report to me in excel file. As i viewed the result early in the morning, i found out that there was 1 stored procedure created last night at around 7:15 PM. I asked persons in our unit if they created this stored procedure but none of them says yes. I need to find out how this stored procedure was created in our production server the question I will be asking is that is it possible for an already created stored procedure to be created again? Or is it possible that when we used a stored procedure its creation date will be updated? Thank you....
"-=Still Learning=-"
Lester Policarpio

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I would like to say, it is possible. To permanently solve this issue, you may save all of existing data somewhere, and compare any change while running your monitoring scripts.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

When you said that you traced to find out the procedure created would can also trace the source from where the command was fired so that youcan easily find the source.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

Thanks for the response guys just found out that a programmer created the stored procedure :D. He was alarmed (because of not notifying us) when i emailed the programmer group cc-ing their supervisor asking about the said sp.

"-=Still Learning=-"

Lester Policarpio

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 4

May I suggest that you don't let your programmers create things on the production servers?

Or if they must, they only get the rights to do so when required, optionally under supervision and at all other times they have limited or no rights. Do you have a change control process in place?

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 5

Actually after the incident i asked my supervisor to limit the access of the programmers to the prod server. I remember him (programmer) saying about an exe file that he used to create the sp. We will take a closer look on the rights of the programmers to the database

thanks for the suggestion highly appreciated....

"-=Still Learning=-"

Lester Policarpio

amit.vaid SSC Rookie Points: 32 More actions · Answer 6

Hi Lester Policarpio,

Can i get this monitoring script which is running daily basis....

regards

amit vaid

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

Yes don't give permissions to vreate procedures in PROD box.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 8

This is for monitoring the Database Creation

INSERT INTO DB_Name.DB_Owner.Table

select name as 'DB Name',

convert(varchar(50),crdate) as 'Date Created',cmptlevel as 'Compatibility Level',

filename as 'File Path',

@@servername as 'Server Name'

from master..sysdatabases

where convert(char(8),crdate,112) > '20080209'--WHERE DATE GREATER THAN THIS. YOU CAN SET YOUR FILTER DATE

order by crdate

This is for the creation of tables, views, sp etc. in all databases in the server

DECLARE @db_names varchar(50)

DECLARE @command varchar(2000)

DECLARE @STR varchar(2000)

DECLARE db_names CURSOR FOR

select name from master..sysdatabases order by name

OPEN db_names

FETCH NEXT FROM db_names INTO @db_names

WHILE(@@FETCH_STATUS = 0)

BEGIN

--MONITORING OF CREATED TABLES,VIEWS,SP,FUNCTION,TRIGGER ETC.

SET @command = 'select name as ''Name'',

CASE WHEN xtype = ''S'' THEN ''System Tables''

WHEN xtype = ''P'' THEN ''Stored Procedure''

WHEN xtype = ''PK'' THEN ''Primary Key''

WHEN xtype = ''U'' THEN ''User Table''

WHEN xtype = ''D'' THEN ''Constraints''

WHEN xtype = ''F'' THEN ''Foreign Key''

WHEN xtype = ''FN'' THEN ''Function''

WHEN xtype = ''V'' THEN ''Views''

WHEN xtype = ''TR'' THEN ''Triggers''

ELSE ''Unknown Table Type''

END as ''Table Type'',convert(varchar(50),crdate) as ''Date Created'',

uid as ''Database Owner ID'','+''''+@db_names+''''+' as ''Database Name'',

@@servername as ''Server_Name''

from '+'['+@db_names+']'+'..sysobjects

where convert(char(8),crdate,112) >''20080209''--WHERE DATE GREATER THAN THIS. YOU CAN SET YOUR FILTER DATE

and name not like ''dt_%''

'

SET @STR = 'INSERT INTO '+'DB_Name.DB_Owner.Table '+@command

exec (@str)

FETCH NEXT FROM db_names INTO @db_names

END

CLOSE db_names

DEALLOCATE db_names

"-=Still Learning=-"

Lester Policarpio

amit.vaid SSC Rookie Points: 32 More actions · Answer 9

hi lester,

its good to receive reply from you,

one thing is there any table with certain column definition needs to be created in required database?.

if yes, do send across to me.

with regards

amit vaid

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 10

If this is what you are asking :

CREATE TABLE [DB_Monitoring] (

[DB_Name] [varchar] (30) ,

[Date_Created] datetime ,

[Compatibility_Level] [int] NULL ,

[File_Path] [varchar] (2500) ,

[Server_Name] [varchar] (30)

) ON [PRIMARY]

GO

CREATE TABLE [table_monitoring] (

[Name] [varchar] (30) ,

[Table_Type] [varchar] (30) ,

[Date_Created] datetime ,

[DB_Owner_ID] [int] NULL ,

[DB_Name] [varchar] (50) ,

[Server_name] [varchar] (30)

) ON [PRIMARY]

GO

"-=Still Learning=-"

Lester Policarpio

amit.vaid SSC Rookie Points: 32 More actions · Answer 11

Hi lester

after creating the table , definition given by you, i am running below:

--This is for the creation of tables, views, sp etc. in all databases in the server

DECLARE @db_names varchar(50)

DECLARE @command varchar(2000)

DECLARE @STR varchar(2000)

DECLARE CLICS CURSOR FOR

select name from master..sysdatabases order by name

OPEN CLICS

FETCH NEXT FROM CLICS INTO @db_names

WHILE(@@FETCH_STATUS = 0)

BEGIN

--MONITORING OF CREATED TABLES,VIEWS,SP,FUNCTION,TRIGGER ETC.

SET @command = 'select name as ''Name'',

CASE WHEN xtype = ''S'' THEN ''System Tables''

WHEN xtype = ''P'' THEN ''Stored Procedure''

WHEN xtype = ''PK'' THEN ''Primary Key''

WHEN xtype = ''U'' THEN ''User Table''

WHEN xtype = ''D'' THEN ''Constraints''

WHEN xtype = ''F'' THEN ''Foreign Key''

WHEN xtype = ''FN'' THEN ''Function''

WHEN xtype = ''V'' THEN ''Views''

WHEN xtype = ''TR'' THEN ''Triggers''

ELSE ''Unknown Table Type''

END as ''Table Type'',convert(varchar(50),crdate) as ''Date Created'',

uid as ''Database Owner ID'','+''''+@db_names+''''+' as ''Database Name'',

@@servername as ''Server_Name''

from '+'['+@db_names+']'+'..sysobjects

where convert(char(8),crdate,112) >''20080209''--WHERE DATE GREATER THAN THIS. YOU CAN SET YOUR FILTER DATE

and name not like ''dt_%''

'

SET @STR = 'INSERT INTO '+'model.dbo.Table_monitoring'+@command

exec (@str)

FETCH NEXT FROM CLICS INTO @db_names

END

CLOSE CLICS

DEALLOCATE CLICS

-------------------------------------------------------

I AM NOW GETTING THE FOLLOWING ERROR MESSAGE

Server: Msg 170, Level 15, State 1, Line 0

Line 1: Incorrect syntax near 'name'.