USE [master]
GO
-- {password}=  before You run the script You must set the password 
IF NOT EXISTS (SELECT * FROM sys.syslogins  WHERE name ='AimBetter')
BEGIN
CREATE LOGIN [AimBetter] WITH PASSWORD= N'{password}',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [AimBetter] FOR LOGIN [AimBetter]
END
GO
IF NOT EXISTS (SELECT * FROM sys.sysusers where name='AimBetter')
EXEC ('CREATE USER [AimBetter] FOR LOGIN [AimBetter] WITH DEFAULT_SCHEMA=[dbo]')
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
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

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_AimBetter_Users', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBA_AimBetter_Users]    Script Date: 4/14/2024 4:17:22 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA_AimBetter_Users', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @all BIT =0
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 @CMD=@CMD+''
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]'''');
END
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]
''
FROM sys.databases
WHERE DATABASEPROPERTYEX ( name , ''Updateability'' ) =''READ_WRITE'' AND
 (@all=1 OR name NOT IN(SELECT DB FROM #DB)) AND
 state =0
IF @CMD<>''''
BEGIN
PRINT @CMD
EXEC(@CMD)
END
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @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=0, 
		@active_start_date=20160727, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'3c884faa-23a7-4058-aa6f-5b5d550b6694'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
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