MSSQL On-Prem / Azure Managed – Create AimBetter SQL User

Create a User for AimBetter

The following instructions are for creating a new SQL Server user named AimBetter with minimal permissions on the primary instance. For a replica instance, check the Replica User for AimBetter session.

If you have an AimBetter user and you want to delete it before creating a new one, proceed with deleting the AimBetter user.

1- Sysadmin check

To create the AimBetter user with minimal permissions, you need to have sysadmin credentials. Run the following script to ensure that the user is a sysadmin.

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

2- Login creation

Create the AimBetter login according to your preferred login authentication type. At this point, no permissions will be granted. For permissions granting, check session 3 below.

— SQL Authentication Login

Run the following script to create an SQL Authentication login named ‘AimBetter’. Before you run the script, substitute {password} with your selected password.

USE [master]
GO  

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  

— Windows Authentication Login

Run the following script to create a Windows Authentication login named ‘AimBetter’. Before you run the script, substitute {password} with your selected password.

USE [master]
GO

IF NOT EXISTS (SELECT * FROM sys.syslogins where name='{Yourdomain\AimBetter}')
BEGIN 
	CREATE LOGIN [Yourdomain\AimBetter] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
	CREATE USER  [Yourdomain\AimBetter] FOR LOGIN [Yourdomain\AimBetter]
END 

3- User Creation with Minimal Permissions

Create the AimBetter user with minimal permissions by running the following script on your SSMS (SQL Server Management Studio), after creating the AimBetter login (session 2 above).

• 👉AimBetter MSSQL User Script

Replica User for AimBetter

To monitor a replica database (secondary Database), such as mirror / log-shipping /Always On, you don’t need to create a new user for AimBetter.

After creating the AimBetter SQL User on the primary Database, follow these instructions:

1. On the primary Database, run this script
   • 👉 Copy Monitor User Script.
2. Copy the output from the above execution.
3. On the secondary Database, run the following script, replacing the word OUTPUT (besides @jobId =) in the script with the above output.
   • 👉AimBetter Replica MSSQL User Script

Deleting the AimBetter User

Deleting the AimBetter user can be helpful when it has a problem and you want to create a new one.

To delete the AimBetter SQL Server user, follow these instructions:

• Run this script with sysadmin credentials

SET NOCOUNT ON;
SELECT 'USE [' + name +']
DROP USER [AimBetter]
GO
'
FROM sys.databases

SELECT '
USE [msdb]
GO
EXEC msdb.dbo.sp_delete_job @job_name=N''DBA_Aim_Better_Users'', @delete_unused_schedule=1
use master
GO
DROP LOGIN [AimBetter]
GO
'

• Click on the “Results to Text” icon as shown in the following image

• Copy all the Result script, marked in the above image
• Run this script with sysadmin credentials

Granting Minimal Permissions Manually

The script for creating an AimBetter user with minimal permissions includes the automation of permission granting for new databases in the monitored SQL Server instance.

If the automation (job) part is excluded from the script and you want to grant minimal permissions for the AimBetter user manually, use one of the following scripts according to your specific needs.

— Script for granting permissions to a single database

Run this script in the database you want to grant permissions for AimBetter monitoring.

IF  NOT EXISTS(SELECT * FROM sys.sysusers WHERE name ='AimBetter')
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]

— Script for granting permissions to multiple databases

Run this script to grant permissions for AimBetter monitoring to multiple databases.

DECLARE @all BIT =0
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DB')IS NOT NULL
DROP TABLE #DB;
CREATE TABLE #DB (DB sysname)
INSERT INTO #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