declare @dbName nvarchar(MAX);
set @dbName = 'SPAGOBI_DATABASE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database
/** Create the Database **/
execute ( '
CREATE DATABASE [' + @dbName + ']
')
/** Compatibility SQLServer 2008 100 **/
/** Compatibility SQLServer 2012 110 **/
/** Compatibility SQLServer 2014 120 **/
/** Compatibility SQLServer 2016 130 **/
/** Compatibility SQLServer 2017 140 **/
execute ( '
ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [' + @dbName + '] SET ALLOW_SNAPSHOT_ISOLATION ON
')
/****** Create the Login [' + @dbName + '] ******/
execute ( '
CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''',
DEFAULT_DATABASE=[' + @dbName + '],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
ALTER LOGIN [' + @dbName + '] ENABLE
')
/** Add the required roles for the created login **/
execute ( '
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + ']
ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + ']
')
execute ( '
CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + '
ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo]
')
/** Give the created login the ownership and connect rights on the created database **/
execute ( '
EXEC sp_addrolemember ''db_owner'', ''' + @dbName + '''
GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa
RECONFIGURE ;
')
/** Give the created database a user linked to the created login and set it as the owner **/
execute ( '
USE [' + @dbName + ']
CREATE USER [' + @dbName + '] FOR LOGIN [' + @dbName + ']
ALTER USER [' + @dbName + '] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + ']
RECONFIGURE ;
')
| 