RSS

Customized Database-Server Alert Part#2

18 Sep

Hi my followers today i will complete on the series of the SQL Server Customized Database-Server Alert as i started the first post Part#1 and i explained on it on of the most important Database alert (SQL Server Database alert) and i do one Stored procedure to monitor the database status and to send Immediate alert and today i will add two new alerts :

Part#1 

Read_only Database Report : 

USE msdb
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

— =============================================
— Author: Mustafa EL.Masry
— Create date: 16/09/2015 01:52 PM
— Description: Report by all Database are in read_only Mode
— We are Exceulded the Drive Name E
— =============================================
Create Proc [dbo].[ReadOnlyDatabasesReport]
@profile_name_P Nvarchar(500) =’DBMailProfile’,@recipients_P Nvarchar(500) =’SQLGULF@MostafaElmasry.com’
AS
begin
Set NOCount on
declare @p_subject Nvarchar(500)
SET @p_subject = N’Databases in Read_Only Mode on DB Cluster ‘ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))

—Send the mail as table Formate
if(select count(*) from sys.databases where is_read_only <> 0 )>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=” color: red; ” >Read_only Mode Databases Report</H2>’ +
N’ <span style=” font-size: 16px;” >Urgnet this list by Databases are in the Read_only mode please take immediate action to fix it or Exclude it from the Alert </span>’ +
N'<table border=”1″>’ +
N'<tr><th>Database Name</th><th>Database Status</th></tr>’ +
CAST ( ( Select td=name, ”,td=is_read_only from sys.databases where is_read_only <> 0
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name=@profile_name_P, –Change to your Profile Name
@recipients=@recipients_P, –Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = ‘HTML’ ;

END
Else Print ‘All Databases are in Read_Write Mode’

END

Backup Running Now 

We know we should not allow for any one to be have Access on the production more than ( Read , Write , Execute ) Except only the DBA but some times some applications it need High access or may be you are going for new environment and you still you didn’t cover all the Security issue so at this time you should track every thing happened on the Production one of the point you need to track it (Database backup) you should be know Who is taking backup from any database on the production Except you or your Third party tools , based on that i do one Stored procedure you need to Schedule it in SQL Server job it will check every 10 SEC the backup running on the server bu which user if this user not assigned in the Stored procedure it will send email direct

USE [msdb]
GO

/****** Object: StoredProcedure [dbo].[BackupRunningNow] Script Date: 29/06/2015 01:01:27 PM ******/
DROP PROCEDURE [dbo].[BackupRunningNow]
GO

/****** Object: StoredProcedure [dbo].[BackupRunningNow] Script Date: 29/06/2015 01:01:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

— =============================================
— Author: Mustafa EL-Masry
–Community :https://mostafaelmasry.com/
— Create date: 25/06/2015
— Description: Check any user trying to do backup or restore
— =============================================

CREATE Proc [dbo].[BackupRunningNow]
@profile_name_P Nvarchar(500) =’DBMailProfile’,@recipients_P Nvarchar(500) =’SQLGULF@MostafaElmasry.com’
AS
begin
Set NOCount on

declare @p_subject Nvarchar(500)
SET @p_subject = N’Non-Authorized user trying to do BACKUP/RESTORE on client’ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar)) + Cast(getdate() As nvarchar(20))
declare @session_id int = (SELECT r.session_id FROM sys.dm_exec_requests r
WHERE command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’))
Create table #BackupTakenbyDIFFuser
(
SessionID int,
login_name Nvarchar(1000),
host_name Nvarchar(1000),
program_name Nvarchar (1000),
start_time Nvarchar(1000),
[T-SQL] Nvarchar(1000)
)
insert into #BackupTakenbyDIFFuser
(SessionID,login_name,host_name,program_name,start_time,[T-SQL])

SELECT
r.session_id,
ES.login_name,
ES.host_name,
ES.program_name ,
R.start_time,
CONVERT(VARCHAR(1000),(
SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle))) AS [T-SQL]
FROM sys.dm_exec_requests r
Inner join sys.dm_exec_sessions AS ES
on R.session_id=ES.session_id
WHERE R.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
and ES.login_name not in (‘Dominname\MostafaElmasry’) —– Put the user name here allowed to take backup

IF
(
Select Count(1) from #BackupTakenbyDIFFuser
)>=1
Begin

DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=” color: red; ” >Actions Out of MOH Policy</H2>’ +
N’ <span style=” font-size: 18px;” > Dear DB Team
Top Urgent!! Non-Authorized user are trying to do BACKUP/RESTORE on Production
Database please take immediate action because this kind of actions are really disrupting our backup plans
and it is out of MOH Policy Please Stop this Action now </span>’ +
N'<table border=”1″>’ +
N'<tr><th>Session ID</th><th>login_name</th><th>host_name</th><th>program_name</th><th>start_time</th><th>T-SQL</th></tr>’ +
CAST ( ( Select td=SessionID, ”,td=login_name, ”,td=host_name, ”,td=program_name, ”,td=start_time, ”,td=[T-SQL]
from #BackupTakenbyDIFFuser
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name=@profile_name_P, –Change to your Profile Name
@recipients=@recipients_P, –Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = ‘HTML’ ;
END

END

Untitled

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS , WHO WE ARE

 
1 Comment

Posted by on September 18, 2015 in Database Alert

 

Tags: , , , , ,

One response to “Customized Database-Server Alert Part#2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s