1. Home
  2. Settings
  3. MSSQL On Prime / Azure Managed – Create monitor user script

MSSQL On Prime / Azure Managed – Create monitor user script

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


-- PASSWORD= N'@A6X6n5Lse'-> before You run the script You must change the password (defult)
USE [master] 
GO
IF NOT EXISTS (SELECT * FROM sys.syslogins  WHERE name ='AimBetter')
BEGIN 
CREATE LOGIN [AimBetter] WITH PASSWORD= N'{passowrd}',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [AimBetter] FOR LOGIN [AimBetter] 
END 
GO
GRANT VIEW ANY DATABASE TO [AimBetter]
GO
GRANT VIEW SERVER STATE TO [AimBetter] 
GO
GRANT VIEW ANY DEFINITION TO [AimBetter] 
GO
IF (SELECT compatibility_level  FROM sys.databases where database_id =2)>100
EXEC ('GRANT ALTER ANY EVENT SESSION TO [AimBetter]')
GO
grant EXECUTE on xp_enumerrorlogs to [AimBetter]
grant EXECUTE on xp_readerrorlog to [AimBetter] 
grant EXECUTE on xp_sqlagent_enum_jobs to [AimBetter] 
 
GO
-------------- --run the script result os sys admin
IF OBJECT_ID('DBA_AimBetter_Users') IS NOT NULL
DROP PROC DBA_AimBetter_Users 
GO
USE master 
GO
CREATE PROC [dbo].[DBA_AimBetter_Users] @all BIT =0
AS
SET NOCOUNT ON 
IF OBJECT_ID('tempdb..#DB')IS NOT NULL
DROP TABLE #DB;
CREATE TABLE #DB (DB sysname)
 
INSERT #DB 
 
EXEC sp_MSforeachdb ' 
USE [?]
IF     CONVERT(NVARCHAR(MAX),DATABASEPROPERTYEX(''?'', ''Updateability'')) LIKE ''READ_WRITE''
BEGIN
SELECT DB_NAME() FROM sys.database_principals where type_desc = ''SQL_USER'' and default_schema_name = ''dbo''
AND name=''AimBetter''
 
END
'



DECLARE @CMD VARCHAR(MAX)=''
SELECT @[email protected]+' 
USE ['+name +'] 
IF  NOT EXISTS(SELECT * FROM sys.sysusers WHERE name =''AimBetter'')
AND        CONVERT(NVARCHAR(MAX),DATABASEPROPERTYEX('''+name +''', ''Updateability'')) LIKE ''READ_WRITE''
BEGIN 
EXEC(''CREATE USER [AimBetter] FOR LOGIN [AimBetter]'');

EXEC sp_addrolemember N''db_ddladmin'', N''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]
END
'
FROM sys.databases
WHERE 
 (@all=1 OR name NOT IN(SELECT DB FROM #DB)) AND
 state =0 
IF @CMD<>''
BEGIN
PRINT @CMD
EXEC(@CMD)
END
GO
EXEC [dbo].[DBA_AimBetter_Users] 
GO
 
USE [msdb] 
GO
IF EXISTS(SELECT * FROM msdb..sysjobs WHERE name =N'DBA_AimBetter_Users')
EXEC msdb.dbo.sp_delete_job @job_name=N'DBA_AimBetter_Users', @delete_unused_schedule=1;
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'DBA_AimBetter_Users',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email= 2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT select @jobId 
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBA_AimBetter_Users'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBA_AimBetter_Users',
@step_name=N'DBA_AimBetter_Users',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DBA_AimBetter_Users',
@database_name=N'master',
@flags=0 
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBA_AimBetter_Users',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
[email protected]_login_name=N'', 
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBA_AimBetter_Users',
@name=N'DBA_AimBetter_Users',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20160727,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT select @schedule_id 
GO
EXEC sp_addrolemember N'db_datareader', N'AimBetter'
GO
EXECUTE sp_addrolemember
@rolename = 'SQLAgentReaderRole',
@membername = 'AimBetter'
GO
EXECUTE sp_addrolemember
@rolename = 'TargetServersRole', @membername = 'AimBetter'
GO
grant execute on dbo.sp_help_jobhistory TO [AimBetter] 
GO
GRANT SELECT ON [dbo].[sysjobschedules] TO [AimBetter] 
GRANT SELECT ON [dbo].[sysjobhistory] TO [AimBetter] 
GRANT SELECT ON [dbo].[sysjobactivity] TO [AimBetter] 
GRANT SELECT ON [dbo].[sysjobs] TO [AimBetter] 
GO 
USE [master]
GO
DBA_AimBetter_Users 1



EXEC sp_addrolemember N'db_ddladmin', N'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







Related Articles