who create new db or drop db

Question

Post reply

who create new db or drop db

sqlfriend

SSC Guru

Points: 52781
More actions
May 4, 2011 at 10:33 am

#235815

Is there a way that I can get an alert that a database is created or dropped and by who in all our sql servers?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check the default trace. (nice articles here on the subject).

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thank you, can you point me a link to the article.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You know there's a search button top right on this page...

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That's a nice one too.

http://www.sqlservercentral.com/articles/Stairway+Series/72464/

Lowell SSC Guru Points: 323518 More actions · Answer 5

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

sqlfriend

SSC Guru

Points: 52781

May 4, 2011 at 10:47 am

#1319918

Thanks a lot

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thanks, the standard report has so many new features I haven't found. I guess thies is recorded by default trace.

Thanks again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

SQLMyFriend (5/4/2011)
Thanks, the standard report has so many new features I haven't found. I guess thies is recorded by default trace.
Thanks again

yes it is.

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

I see the report of schema change history, I see our report is huge, I export to excel to do some filtering.

If MS can add some filter on the report itself, that would be very helpful.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

SQLMyFriend (5/4/2011)
I see the report of schema change history, I see our report is huge, I export to excel to do some filtering.
If MS can add some filter on the report itself, that would be very helpful.

Post a thread on connect and propose the idea.

Plan A would be the find the Rdl file in question, edit it to your needs and move on with it. I garantee it'll be faster that way (assuming you can edit those reports out)! :w00t:

MichaelJasson SSCrazy Points: 2732 More actions · Answer 11

You can create trigger for that -

IF EXISTS (SELECT * FROM sys.server_triggers

WHERE name = 'ddl_trig_database')

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR DROP_DATABASE

AS

PRINT 'Database Droped.'

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

GO

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

MichaelJasson SSCrazy Points: 2732 More actions · Answer 12

You can create trigger for that-

IF EXISTS (SELECT * FROM sys.server_triggers

WHERE name = 'ddl_trig_database')

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR DROP_DATABASE

AS

PRINT 'Database Droped.'

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

GO

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.