SQL Query to Create Databases on MS SQL for Installation Using a Domain User

The MSSQL_Create_Databases_v4.sql is available at the bottom of this page.

Limitations

The SQL query can creates the Data Warehouse (DWH) on the same server as the main database.
To install the Data Warehouse (DWH) database on another SQL server, set @createdwh to 'false' and create the database manually on the other server.

Prerequisite

Make sure that that the database collation (preferably Case-Insensitive and Accent-Sensitive (language_CI_AS)) and location of files on the disk is set as expected on Model DB as these will be inherited by newly created databases.
Prepare three domain user accounts, these users must belong to the same domain:
- domain\user_1
  - User account that has "sysadmin" rights, typically SQL server administrator.
  - This user will be used to connect to the MS SQL server in order to launch the SQL query to create databases and configure their settings settings.
- domain\user_2
  - User account that has local administrator and "Log on as a service" rights on the YSoft SafeQ Management Server, typically a service account.
  - This account must be defined MSSQL_Create_Databases_v4.sql .
  - This account will obtain the "db_owner" for the databases created by the SQL query.
  - This account will be used as "Run As" for the installation package and for running the YSoft SafeQ services.
    Note: Whenever specifying/using domain\user_2, make sure to use the same syntax in a way MS SQL server knows it. For example if your domain accounts on MS SQL server are listed as domain\user_2, do NOT use syntax domain.tst.com\user_2 in .sql script or when running the installation. Otherwise e.g. default database schema might not be set properly.
- domain\user_3
  - User account has full administrative rights on YSoft SafeQ Management Server, typically a server administrator.
  - This user will be used for authentication to server before launching the installation.
    Note: This account can be actually the same one as domain\user_2 provided he has the combination of rights from both accounts.

Steps

Edit MSSQL_Create_Databases_v4.sql and set the following parameters:
1. @login: set the user for database access ( domain\user_2).
2. @dbname (optional): alter the database name prefix. Default: 'SQDB6'.
3. @createdwh (optional): set "true" if you want to create separate database for the Data Warehouse (DWH). Values: true/false.
4. @createyps (optional): set "true" if you want to create database for the Payment System (YPS) database. Values: true/false.
Log in to MS SQL server as domain\user_1 .
1. Run " MSSQL_Create_Databases_v4.sql ".
2. Verify that the databases were created and that domain\user_2 has the db_owner rights.
Log in to YSoft SafeQ server as domain\user_3.
1. Right-click the installation package while holding SHIFT > select "Run as different user" > type in credentials of domain\user_2 > finish the installation.
2. Verify that the YSoft SafeQ services are running under the domain\user_2 account.

Script

MSSQL_Create_Databases_v4.sql

SQL

USE master
GO
/* Enable Contained Databases */
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
 
/******************* DO NOT EDIT ABOVE THIS LINE *******************/
 
/* EDIT @login, @dbname, @createdwh and @createyps ONLY! */
 
/* Set the name of the domain user that SafeQ uses to access the database */
DECLARE @login nvarchar(50) = 'domain\user_2';
 
/* Set the name of the SafeQ database */
DECLARE @dbname nvarchar(30) = 'SQDB6';
 
/* Create or not the Data Warehouse (DWH) database : true/false */
DECLARE @createdwh bit = 'false'
 
/* Create or not the Payment System (YPS) database : true/false */
DECLARE @createyps bit = 'false'
 
/******************* DO NOT EDIT BELOW THIS LINE *******************/
 
DECLARE @dbnameims nvarchar(30) = @dbname + '_IMS';
DECLARE @dbnamedwh nvarchar(30) = @dbname + '_DWH';
DECLARE @dbnameyps nvarchar(30) = @dbname + '_YPS';
 
/* Raise an error if the user connected is the same as the user that SafeQ uses to access the database */
IF (SYSTEM_USER = @login)
BEGIN
raiserror('You must be logged in into the Windows Server with a different user than the one which will be used for communication between YSoft SafeQ installer and the databases.', 11, 0)
RETURN
END;
 
/* Raise an error if the user connected is from a different domain than the user that SafeQ uses to access the database */
IF ((SELECT SUBSTRING(SYSTEM_USER,0,CHARINDEX('\',SYSTEM_USER,0)) AS firstpart) <> (SELECT SUBSTRING(@login,0,CHARINDEX('\',@login,0)) AS firstpart))
BEGIN
raiserror('You must be logged in into the Windows Server with a user from the same domain than the one which will be used for communication between YSoft SafeQ installer and the databases.', 11, 0)
RETURN
END;
 
/* If the "SQDB6" exists, get exclusive access to the database before dropping it */
IF ((DB_ID(@dbname) IS NOT NULL))
BEGIN
EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC ('DROP DATABASE ' + @dbname);
END;
 
/* If the "SQDB6_IMS" exists, get exclusive access to the database before dropping it */
IF ((DB_ID(@dbnameims) IS NOT NULL))
BEGIN
EXEC ('ALTER DATABASE ' + @dbnameims + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC ('DROP DATABASE ' + @dbnameims);
END;
 
/* If the "SQDB6_DWH" exists, get exclusive access to the database before dropping it */
IF ((DB_ID(@dbnamedwh) IS NOT NULL))
BEGIN
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC ('DROP DATABASE ' + @dbnamedwh);
END;
 
/* If the "SQDB6_YPS" exists, get exclusive access to the database before dropping it */
IF ((DB_ID(@dbnameyps) IS NOT NULL))
BEGIN
EXEC ('ALTER DATABASE ' + @dbnameyps + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC ('DROP DATABASE ' + @dbnameyps);
END;
 
/*  Set the database recovery mode. If AlwaysOn is enabled, it will be set to FULL. Otherwise, it will be set to SIMPLE */
DECLARE @recovery nvarchar(30)
IF ((SELECT SERVERPROPERTY ('IsHadrEnabled')) = 1)
    BEGIN
        SET @recovery = 'FULL';
    END
ELSE
    BEGIN
        SET @recovery = 'SIMPLE';
    END  
 
/* Create the "SQDB6" database */
EXEC ('CREATE DATABASE ' + @dbname);
EXEC ('ALTER DATABASE ' + @dbname + ' SET CONTAINMENT = PARTIAL');
EXEC ('ALTER DATABASE ' + @dbname + ' SET RECOVERY ' + @recovery);
EXEC ('ALTER DATABASE ' + @dbname + ' SET ALLOW_SNAPSHOT_ISOLATION ON');
EXEC ('ALTER DATABASE ' + @dbname + ' SET READ_COMMITTED_SNAPSHOT ON');
 
/* Create the "SQDB6_IMS" database */
EXEC ('CREATE DATABASE ' + @dbnameims);
EXEC ('ALTER DATABASE ' + @dbnameims + ' SET CONTAINMENT = PARTIAL');
EXEC ('ALTER DATABASE ' + @dbnameims + ' SET RECOVERY ' + @recovery);
EXEC ('ALTER DATABASE ' + @dbnameims + ' SET ALLOW_SNAPSHOT_ISOLATION ON');
EXEC ('ALTER DATABASE ' + @dbnameims + ' SET READ_COMMITTED_SNAPSHOT ON');
 
/* Create the "SQDB6_DWH" database */
IF (@createdwh = 1)
BEGIN
EXEC ('CREATE DATABASE ' + @dbnamedwh);
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET CONTAINMENT = PARTIAL WITH NO_WAIT');
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET TRUSTWORTHY ON');
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET RECOVERY ' + @recovery);
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET ALLOW_SNAPSHOT_ISOLATION ON');
EXEC ('ALTER DATABASE ' + @dbnamedwh + ' SET READ_COMMITTED_SNAPSHOT ON');
END;
 
/* Create the "SQDB6_YPS" database */
IF (@createyps = 1)
BEGIN
EXEC ('CREATE DATABASE ' + @dbnameyps);
EXEC ('ALTER DATABASE ' + @dbnameyps + ' SET CONTAINMENT = PARTIAL');
EXEC ('ALTER DATABASE ' + @dbnameyps + ' SET RECOVERY ' + @recovery);
EXEC ('ALTER DATABASE ' + @dbnameyps + ' SET ALLOW_SNAPSHOT_ISOLATION ON');
EXEC ('ALTER DATABASE ' + @dbnameyps + ' SET READ_COMMITTED_SNAPSHOT ON');
END;
 
/* Create the login that SafeQ uses to access the database */
/* If the login already exists, remove the server role "sysadmin" */
IF (SUSER_ID(@login) IS NULL)
BEGIN
EXEC ('CREATE LOGIN [' + @login + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]');
END;
ELSE
BEGIN
EXEC ('ALTER SERVER ROLE [sysadmin] DROP MEMBER [' + @login + ']');
END;
 
/* Set access to the "SQDB6" database */
EXEC ('use ' + @dbname + ' CREATE USER [' + @login + '] FOR LOGIN [' + @login + '] WITH DEFAULT_SCHEMA=[dbo]');
EXEC ('use ' + @dbname + ' EXEC sp_addrolemember ''db_owner'',''' + @login + '''');
EXEC ('use ' + @dbname + ' EXEC sp_changedbowner ''sa''');
 
/* Set access to the "SQDB6_IMS" database */
EXEC ('use ' + @dbnameims + ' CREATE USER [' + @login + '] FOR LOGIN [' + @login + '] WITH DEFAULT_SCHEMA=[dbo]');
EXEC ('use ' + @dbnameims + ' EXEC sp_addrolemember ''db_owner'',''' + @login + '''');
EXEC ('use ' + @dbnameims + ' EXEC sp_changedbowner ''sa''');
 
/* Set access to the "SQDB6_DWH" database */
IF (@createdwh = 1)
BEGIN
EXEC ('use ' + @dbnamedwh + ' CREATE USER [' + @login + '] FOR LOGIN [' + @login + '] WITH DEFAULT_SCHEMA=[dbo]');
EXEC ('use ' + @dbnamedwh + ' EXEC sp_addrolemember ''db_owner'',''' + @login + '''');
EXEC ('use ' + @dbnamedwh + ' EXEC sp_changedbowner ''sa''');
END;
 
/* Set access to the "SQDB6_YPS" database */
IF (@createyps = 1)
BEGIN
EXEC ('use ' + @dbnameyps + ' CREATE USER [' + @login + '] FOR LOGIN [' + @login + '] WITH DEFAULT_SCHEMA=[dbo]');
EXEC ('use ' + @dbnameyps + ' EXEC sp_addrolemember ''db_owner'',''' + @login + '''');
EXEC ('use ' + @dbnameyps + ' EXEC sp_changedbowner ''sa''');
END;