Azure MSSQL Database – Create secured monitor user script

Select the master database in your SSMS, after establishing the connection to the Azure SQL Database.

Copy and Run the following scripts on you SSMS tool with sysadmin permssion:

User creation

-- PASSWORD= N'{passowrd}'-> before You run the script You must change the password (defult)
USE [master] 
GO
IF NOT EXISTS (SELECT * FROM sys.sql_logins  WHERE name ='AimBetter')
BEGIN 
CREATE LOGIN [AimBetter] WITH PASSWORD= N'{password}'
ALTER LOGIN [AimBetter] ENABLE
CREATE USER [AimBetter] FOR LOGIN [AimBetter] 
END 

Custom permissions

Select the correct Azure SQL Database and run the following script.

--USE [FOR EACH DATABASE]
IF NOT EXISTS (SELECT * FROM sys.sysusers  WHERE name ='AimBetter')
	CREATE USER [AimBetter] FOR LOGIN [AimBetter] 
GRANT VIEW DATABASE STATE TO [AimBetter] 
GRANT VIEW  DEFINITION TO [AimBetter] 

GRANT ALTER ANY DATABASE EVENT SESSION TO [AimBetter]
EXEC sp_addrolemember N'db_ddladmin', N'AimBetter'
ALTER ROLE db_ddladmin ADD MEMBER  [AimBetter];

DENY ALTER ANY ASSEMBLY                    TO [AimBetter]
DENY ALTER ANY ASYMMETRIC KEY              TO [AimBetter]
DENY ALTER ANY CERTIFICATE                 TO [AimBetter]
DENY ALTER ANY CONTRACT                    TO [AimBetter]
DENY ALTER ANY DATABASE DDL TRIGGER        TO [AimBetter]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [AimBetter]
DENY ALTER ANY DATASPACE                   TO [AimBetter]
DENY ALTER ANY FULLTEXT CATALOG            TO [AimBetter]
DENY ALTER ANY MESSAGE TYPE                TO [AimBetter]
DENY ALTER ANY REMOTE SERVICE BINDING      TO [AimBetter]
DENY ALTER ANY ROUTE                       TO [AimBetter]
DENY ALTER ANY SCHEMA                      TO [AimBetter]
DENY ALTER ANY SERVICE                     TO [AimBetter]
DENY ALTER ANY SYMMETRIC KEY               TO [AimBetter]
DENY CHECKPOINT                            TO [AimBetter]
DENY CREATE AGGREGATE                      TO [AimBetter]
DENY CREATE DEFAULT                        TO [AimBetter]
DENY CREATE FUNCTION                       TO [AimBetter]
DENY CREATE PROCEDURE                      TO [AimBetter]
DENY CREATE QUEUE                          TO [AimBetter]
DENY CREATE RULE                           TO [AimBetter]
DENY CREATE SYNONYM                        TO [AimBetter]
DENY CREATE TABLE                          TO [AimBetter]
DENY CREATE TYPE                           TO [AimBetter]
DENY CREATE VIEW                           TO [AimBetter]
DENY CREATE XML SCHEMA COLLECTION          TO [AimBetter]
DENY REFERENCES                            TO [AimBetter]




GO 

--SQLERRORS
CREATE OR ALTER   PROC [dbo].[MonTargetSQLErrorsV3]
WITH EXECUTE AS OWNER
AS 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @createdate DATETIME

EXEC sp_executesql N'SELECT @createdate = create_time FROM sys.dm_xe_database_sessions  s WHERE name = ''MonTargetSQLErrorsV3''',N'@createdate DATETIME OUTPUT',@createdate=@createdate OUTPUT
IF (@createdate IS NULL)
BEGIN



EXEC sp_executesql N'
		CREATE EVENT SESSION [MonTargetSQLErrorsV3] ON DATABASE  
		ADD EVENT sqlserver.error_reported(
			ACTION(sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_name,sqlserver.username,sqlserver.sql_text,client_hostname)
			WHERE ([severity]>10 AND [client_pid]>0))  ,
		ADD EVENT sqlserver.attention(
			ACTION(sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_name,sqlserver.username,sqlserver.sql_text,client_hostname)
			WHERE ([sqlserver].[client_pid]>0))
		ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(8192))
		WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=Per_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)

		ALTER EVENT SESSION [MonTargetSQLErrorsV3] ON DATABASE  STATE=START;

		IF NOT EXISTS (SELECT create_time FROM sys.dm_xe_database_sessions  s WHERE name = ''MonTargetSQLErrorsV3'')
		   ALTER EVENT SESSION [MonTargetSQLErrorsV3] ON DATABASE  STATE=START;

'



END




IF(@createdate < DATEADD(HOUR,-1,GETDATE()))
 BEGIN

  SET @createdate = NULL;
 EXEC sp_executesql N'ALTER EVENT SESSION [MonTargetSQLErrorsV3] ON DATABASE STATE=STOP;'
 EXEC sp_executesql N'ALTER EVENT SESSION [MonTargetSQLErrorsV3] ON DATABASE STATE=START;'
 END 
GO

--DeadLock
CREATE OR ALTER   PROC [dbo].[MontargetDeadLockV3]
WITH EXECUTE AS OWNER
AS 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @createdate DATETIME

EXEC sp_executesql N'SELECT @createdate = create_time FROM sys.dm_xe_database_sessions  s WHERE name = ''MontargetDeadLockV3''',N'@createdate DATETIME OUTPUT',@createdate=@createdate OUTPUT
IF (@createdate IS NULL)
BEGIN

EXEC sp_executesql N'
CREATE EVENT SESSION [MontargetDeadLockV3] ON DATABASE  
ADD EVENT sqlserver.database_xml_deadlock_report 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=Per_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)

IF NOT EXISTS (SELECT create_time FROM sys.dm_xe_database_sessions  s WHERE name = ''MontargetDeadLockV3'')
	ALTER EVENT SESSION [MontargetDeadLockV3] ON DATABASE  STATE=START;
'

END




IF(@createdate < DATEADD(HOUR,-1,GETDATE()))
 BEGIN

  SET @createdate = NULL;
 EXEC sp_executesql N'ALTER EVENT SESSION [MontargetDeadLockV3] ON DATABASE STATE=STOP;'
 EXEC sp_executesql N'ALTER EVENT SESSION [MontargetDeadLockV3] ON DATABASE STATE=START;'
 END 


GO
GRANT EXECUTE ON [dbo].[MonTargetSQLErrorsV3] TO [AimBetter]
GO
GRANT EXECUTE ON [dbo].[MontargetDeadLockV3] TO [AimBetter]
GO