Scripts to Create restricted Database Users

Creating restricted Database User via Script

The following scripts help you to create new Database Users. The privileges are the same level as the Database Users created automatically by SAP when upgrading to or installing SAP Business One Version 10.0.

MS SQL

-- Replace the parameters below with your values (remove the '<' and '>')

DECLARE @userName AS NVARCHAR(50) = '<MyDatabaseUserName>';
DECLARE @passwd AS NVARCHAR(255) = '<MyDatabaseUserPassword>';
DECLARE @databaseName AS NVARCHAR(255) = '<MyCompanyDatabase>';


-- Do not change any code below this line.

DECLARE @UserCreationScript AS VARCHAR(MAX) = '
CREATE LOGIN [{userName}] WITH PASSWORD = N''{passwd}'', DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
 
USE [{databaseName}]
CREATE USER [{userName}] FOR LOGIN [{userName}];
ALTER USER [{userName}] WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_owner] ADD MEMBER [{userName}];'
 
SET @UserCreationScript = REPLACE(REPLACE(@UserCreationScript, '{userName}', @userName), '{passwd}', @passwd)
SET @UserCreationScript = REPLACE(@UserCreationScript, '{databaseName}', @databaseName)
 
EXECUTE(@UserCreationScript)

HANA

DO BEGIN

-- Replace the parameters below with your values (remove the '<' and '>')

DECLARE aUser nvarchar(50) = '<MyDatabaseUserName>';
DECLARE aPassword nvarchar(255) = '<MyDatabaseUserPassword>';
DECLARE aCompany nvarchar(255) = '<MyCompanyDatabase>';

-- Do not change any code below this line.

EXEC 'CREATE USER ' || aUser || ' PASSWORD ' || aPassword;
EXEC 'ALTER USER ' || aUser || ' DISABLE PASSWORD LIFETIME';
EXEC 'GRANT PAL_ROLE TO ' || aUser;
EXEC 'GRANT CATALOG READ TO ' || aUser;
EXEC 'GRANT ALTER, CREATE ANY, DEBUG, DELETE, DROP, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, TRIGGER, UPDATE ON SCHEMA ' || aCompany || ' TO ' || aUser;
EXEC 'GRANT SELECT ON "SBOCOMMON"."SLSC" TO ' || aUser;
EXEC 'GRANT SELECT ON "SBOCOMMON"."SDRQ" TO ' || aUser;

END;

The HANA script was updated on 20.05.2024 as with SAP Business One Version 10.0 FP2305 onwards the standard SAP Database User has access to the SBO-COMMON database.

The SQL script was updated on 11.03.2020 as with SAP Business One Version 10.0 PL01 the Database Users should not have access to the SBO-COMMON database. (To be checked for FP2305)