a new db is created

Question

Post reply

a new db is created

sqlfriend

SSC Guru

Points: 52794
More actions
July 26, 2011 at 1:09 pm

#241454

Is there a sql alert that tells a new db is created?
or is there a simple way to setup to notify dba about a new db is created?
Thanks

Viewing 10 posts - 1 through 10 (of 10 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

I'd check the default trace, I'm sure it's a logged event.

From there on out it's a matter of checking regularly...

Having issues with users creating dbs without your knowledge?

Lowell SSC Guru Points: 323546 More actions · Answer 2

also, you can use a server wide DDL trigger for CREATE_DATABASE, but if the database is created new from a restore, it's not detected, for whatever reason that;s not a CREATE_DATABASE event but a RESOTRE event instead(and that doesn't count as a DDL event)

....you'd have to use an event notification to track that.

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!

Lowell SSC Guru Points: 323546 More actions · Answer 3

i was playing witht eh event notifications for that for a while; on a certians erver, i was trying to create a suite of roles in any database that was created or restored; i figured if i could create a role based on the RESTORE_DATABASE or CREATE_DATABASE events, i could do all sorts of things...need to pull that back out and play with it.

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!

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

What version of sql server are you using? if it is 2008 you have chnace to use policy based management.

Or else here is the default trace[/url]

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Can you explain a little in detail how I can be alerted by using default trace or using policy management when there is a new database created?

Thanks

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

What version are you using.?

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlfriend SSC Guru Points: 52794 More actions · Answer 7

sqlfriend

SSC Guru

Points: 52794

July 26, 2011 at 2:32 pm

#1358839

SQL server 2008 standard

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 8

you can do this multiple ways

1. Trigger

CREATE trigger [ddl_CreateDatabase]

on ALL Server

for Create_database

As

If context_info() 0x4C6F6C20596F752064696420646F206974203A44 — backdoor

begin

RAISERROR (‘Creating databases has been disalowed’,10, 1)

Rollback;

end

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [ddl_CreateDatabase] ON ALL SERVER

2. WMI Alert WMI[/url]

3. Some other Notification Method

--Create a queue to receive messages.

CREATE QUEUE NotifyQueue ;

GO

--Create a service on the queue that references

--the event notifications contract.

CREATE SERVICE NotifyService

ON QUEUE NotifyQueue

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

--Create a route on the service to define the address

--to which Service Broker sends messages for the service.

CREATE ROUTE NotifyRoute

WITH SERVICE_NAME = 'NotifyService',

ADDRESS = 'LOCAL';

GO

--Create the event notification.

CREATE EVENT NOTIFICATION log_ddl1

ON SERVER

FOR Object_Created

TO SERVICE 'NotifyService',

'8140a771-3c4b-4479-8ac0-81008ab17984' ;

Reference

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

chandan_jha18 SSChampion Points: 11330 More actions · Answer 9

Using default trace can give you answers but if your server has some heavy activity going, the default traces will be wiped out soon. Also, I am not sure but you cannot read from a system trace file if it is currently used. I had tried that but failed so had to copy that file to some other location and then read from it.

I will prefer using triggers or some other notification methods as described above.