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