January 4, 2017 at 7:49 am
is there any pre-existing alert that I can use or do I need to create a script? if so then which tables shall tell me of what's new?
January 4, 2017 at 7:57 am
I don't believe there is an alert for this no.
You could use triggers. For users you would need to put a trigger on each database you want to check for user creation on. To check for database creation, you would need a server level trigger.
Be careful, as if using triggers, you will need to ensure that logins or users that are allowed to create databases and/or users have access to perform the actions the trigger does, otherwise that will receive a permission denied, or similar, error.
Of ocurse, there might be a better way than this, just one option.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 7:59 am
You could create a server-level trigger to fire on the CREATE_DATABASE event. Or you could periodically query sys.databases to see whether any database has a create_date less than a certain time old.
John
January 4, 2017 at 8:08 am
Also, when you say "User", do you actually mean user, or do you mean login (or even both)? These are not the same thing in SQL, and it surprises me how many people reference them interchangeably.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 8:23 am
mandavli (1/4/2017)
is there any pre-existing alert that I can use or do I need to create a script? if so then which tables shall tell me of what's new?
Why did you mark your own question also as its own solution, I wonder?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 4, 2017 at 8:36 am
Phil Parkin (1/4/2017)
mandavli (1/4/2017)
is there any pre-existing alert that I can use or do I need to create a script? if so then which tables shall tell me of what's new?Why did you mark your own question also as its own solution, I wonder?
Obviously the OP solved his own problem and didn't want to share his answer 😀
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 10:19 am
you might be able to use Event Notifications, I've used it for a number of alert type things before.
http://www.sqlservercentral.com/articles/Event+Notifications/68831/
January 4, 2017 at 3:48 pm
I have a job that polls periodically by calling a stored proc to alert me to new, offline or deleted databases on the servers that I monitor. I have an administrative database that holds a comparison table and the stored proc that does the trick. You will need to put in the e-mail that you want the notification sent to and replace all of the occurrences of <<Server>> with the instance or server name that you want displayed. It's kind of old school but it works. I am sure that it can be automated more and expanded to hold results from more than one instance if you want to poll several different servers but store everything on a single central monitoring database. Hope this helps.
CREATE TABLE dbo.DBListCompare (
polldt DATETIME,
dbname VARCHAR(255),
crtdte DATETIME,
stdesc VARCHAR(20))
GO
CREATE PROCEDURE dbo.NotifyNewOfflineDeletedDBs
AS
/************************************************************************/
/* Stored Procedure Name: NotifyNewOfflineDeletedDBs */
/* Version:1.0 */
/* Author: Lori Brown @SQLSupahStah */
/* Purpose/Description: Check status of databases */
/* Execute syntax: EXEC NotifyNewOfflineDeletedDBs */
/* Input Parameters: None */
/* Output Parameters: None */
/* Views: None */
/* Tables:DBListCompare */
/* sys.databases */
/* Procedures Called:sp_send_dbmail */
/* CHANGE HISTORY: */
/* VersionDate Modified Description of Modification */
/* ------- ------------- ------------------------------------- */
/* */
/************************************************************************/
DECLARE @newdbcnt INT
DECLARE @offlinedbcnt INT
DECLARE @deleteddbcnt INT
DECLARE @body1 VARCHAR(1000)
DECLARE @lastpoll DATETIME
SELECT @lastpoll = MAX(polldt) FROM DBListCompare --get the last polled datetime
-- look for new databases
SELECT @newdbcnt = COUNT(*)
FROM sys.databases
WHERE create_date > @lastpoll
IF @newdbcnt > 0
BEGIN
SET @body1='<table border="2" cellspacing="2" cellpadding="2">
<TR><td colspan=2
style="color: #A0522D; font-family: Verdana; font-size: 12;" align=left>'
SET @body1=@body1+'New Databases</TD></TR>'
SELECT @body1=@body1 +'<TR><TD>'+ name +'</TD></TR>' FROM sys.databases
WHERE create_date > @lastpoll
-- Send an html formatted e-mail to notify
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail', --<<
@recipients = 'DBA@Company.com',
@subject ='New Databases on <<Server>>', --<<
@body = @body1,
@body_format = 'HTML';
--print @body1
END
SET @body1=''
-- look for databases that are offline
SELECT @offlinedbcnt = COUNT(c.name)
FROM sys.databases c
JOIN DBListCompare o ON (c.name = o.dbname)
WHERE c.state_desc = 'OFFLINE'
AND o.stdesc = 'ONLINE'
IF @offlinedbcnt > 0
BEGIN
SET @body1='<table border="2" cellspacing="2" cellpadding="2">
<TR><td colspan=2
style="color: #A0522D; font-family: Verdana; font-size: 12;" align=left>'
SET @body1=@body1+'Offline Databases</TD></TR>'
SELECT @body1=@body1 +'<TR><TD>'+ name +'</TD></TR>' FROM sys.databases c
JOIN DBListCompare o ON (c.name = o.dbname)
WHERE c.state_desc = 'OFFLINE'
AND o.stdesc = 'ONLINE'
-- Send an html formatted e-mail to notify
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail', --<<
@recipients = 'DBA@Company.com',
@subject ='Databases Taken Offline on <<Server>>', --<<
@body = @body1,
@body_format = 'HTML';
--print @body1
END
SET @body1=''
-- look for databases that have been dropped
SELECT @deleteddbcnt = COUNT(o.dbname)
FROM DBListCompare o
LEFT JOIN sys.databases c ON (c.name = o.dbname)
WHERE c.name IS NULL
IF @deleteddbcnt > 0
BEGIN
SET @body1='<table border="2" cellspacing="2" cellpadding="2">
<TR><td colspan=2
style="color: #A0522D; font-family: Verdana; font-size: 12;" align=left>'
SET @body1=@body1+'Deleted Databases</TD></TR>'
SELECT @body1=@body1 +'<TR><TD>'+ dbname +'</TD></TR>' FROM DBListCompare o
LEFT JOIN sys.databases c ON (c.name = o.dbname)
WHERE c.name IS NULL
-- Send an html formatted e-mail to notify
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail', --<<
@recipients = 'DBA@Company.com',
@subject ='Databases Deleted on <<Server>>', --<<
@body = @body1,
@body_format = 'HTML';
--print @body1
END
SET @body1=''
-- empty
TRUNCATE TABLE DBListCompare
-- fill it backup in anticipation of the next polling
INSERT INTO DBListCompare
SELECT GETDATE(), name, create_date, state_desc
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply