Boilerplates/Windows/WS1/accessOVAdeployment.md

Script for SQL Server Database

Copy the script to your Microsoft SQL Server and open a new Query, paste the script and change the <saasdb>, <password>, <loginusername> with the ones you desire.

You can use replace by just using the keyboard shortcut CTRL+H.

CREATE DATABASE <saasdb>
COLLATE Latin1_General_CS_AS;
ALTER DATABASE <saasdb> SET READ_COMMITTED_SNAPSHOT ON;
GO
BEGIN
CREATE LOGIN <loginusername> WITH PASSWORD = N'<password>';
END

USE <saasdb>; 
IF EXISTS (SELECT * FROM sys.database_principals WHERE name=N'<loginusername>')
DROP USER [<loginusername>]

CREATE USER [<loginusername>] FOR LOGIN [<loginusername>]
WITH DEFAULT_SCHEMA=saas;
GO
CREATE SCHEMA saas AUTHORIZATION <loginusername>
GO
GRANT ALTER TO [<loginusername>]
GO
GRANT ALTER ANY APPLICATION ROLE TO [<loginusername>]
GO
GRANT ALTER ANY ASSEMBLY TO [<loginusername>]
GO
GRANT ALTER ANY ASYMMETRIC KEY TO [<loginusername>]
GO
GRANT ALTER ANY CERTIFICATE TO [<loginusername>]
GO
GRANT ALTER ANY COLUMN ENCRYPTION KEY TO [<loginusername>]
GO
GRANT ALTER ANY COLUMN MASTER KEY TO [<loginusername>]
GO
GRANT ALTER ANY CONTRACT TO [<loginusername>]
GO
GRANT ALTER ANY DATABASE AUDIT TO [<loginusername>]
GO
GRANT ALTER ANY DATABASE DDL TRIGGER TO [<loginusername>]
GO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [<loginusername>]
GO
GRANT ALTER ANY DATASPACE TO [<loginusername>]
GO
GRANT ALTER ANY EXTERNAL DATA SOURCE TO [<loginusername>]
GO
GRANT ALTER ANY EXTERNAL FILE FORMAT TO [<loginusername>]
GO
GRANT ALTER ANY EXTERNAL LIBRARY TO [<loginusername>]
GO
GRANT ALTER ANY FULLTEXT CATALOG TO [<loginusername>]
GO
GRANT ALTER ANY MASK TO [<loginusername>]
GO
GRANT ALTER ANY MESSAGE TYPE TO [<loginusername>]
GO
GRANT ALTER ANY REMOTE SERVICE BINDING TO [<loginusername>]
GO
GRANT ALTER ANY ROLE TO [<loginusername>]
GO
GRANT ALTER ANY ROUTE TO [<loginusername>]
GO
GRANT ALTER ANY SCHEMA TO [<loginusername>]
GO
GRANT ALTER ANY SECURITY POLICY TO [<loginusername>]
GO
GRANT ALTER ANY SENSITIVITY CLASSIFICATION TO [<loginusername>]
GO
GRANT ALTER ANY SERVICE TO [<loginusername>]
GO
GRANT ALTER ANY SYMMETRIC KEY TO [<loginusername>]
GO
GRANT ALTER ANY USER TO [<loginusername>]
GO
GRANT AUTHENTICATE TO [<loginusername>]
GO
GRANT BACKUP DATABASE TO [<loginusername>]
GO
GRANT BACKUP LOG TO [<loginusername>]
GO
GRANT CHECKPOINT TO [<loginusername>]
GO
GRANT CONNECT TO [<loginusername>]
GO
GRANT CONNECT REPLICATION TO [<loginusername>]
GO
GRANT CONTROL TO [<loginusername>]
GO
GRANT CREATE AGGREGATE TO [<loginusername>]
GO
GRANT CREATE ASSEMBLY TO [<loginusername>]
GO
GRANT CREATE ASYMMETRIC KEY TO [<loginusername>]
GO
GRANT CREATE CERTIFICATE TO [<loginusername>]
GO
GRANT CREATE CONTRACT TO [<loginusername>]
GO
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [<loginusername>]
GO
GRANT CREATE DEFAULT TO [<loginusername>]
GO
GRANT CREATE EXTERNAL LANGUAGE TO [<loginusername>]
GO
GRANT CREATE EXTERNAL LIBRARY TO [<loginusername>]
GO
GRANT CREATE FULLTEXT CATALOG TO [<loginusername>]
GO
GRANT CREATE FUNCTION TO [<loginusername>]
GO
GRANT CREATE MESSAGE TYPE TO [<loginusername>]
GO
GRANT CREATE PROCEDURE TO [<loginusername>]
GO
GRANT CREATE QUEUE TO [<loginusername>]
GO
GRANT CREATE REMOTE SERVICE BINDING TO [<loginusername>]
GO
GRANT CREATE ROLE TO [<loginusername>]
GO
GRANT CREATE ROUTE TO [<loginusername>]
GO
GRANT CREATE RULE TO [<loginusername>]
GO
GRANT CREATE SCHEMA TO [<loginusername>]
GO
GRANT CREATE SERVICE TO [<loginusername>]
GO
GRANT CREATE SYMMETRIC KEY TO [<loginusername>]
GO
GRANT CREATE SYNONYM TO [<loginusername>]
GO
GRANT CREATE TABLE TO [<loginusername>]
GO
GRANT CREATE TYPE TO [<loginusername>]
GO
GRANT CREATE VIEW TO [<loginusername>]
GO
GRANT CREATE XML SCHEMA COLLECTION TO [<loginusername>]
GO
GRANT DELETE TO [<loginusername>]
GO
GRANT EXECUTE TO [<loginusername>]
GO
GRANT EXECUTE ANY EXTERNAL SCRIPT TO [<loginusername>]
GO
GRANT INSERT TO [<loginusername>]
GO
GRANT REFERENCES TO [<loginusername>]
GO
GRANT SELECT TO [<loginusername>]
GO
GRANT SHOWPLAN TO [<loginusername>]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [<loginusername>]
GO
GRANT TAKE OWNERSHIP TO [<loginusername>]
GO
GRANT UNMASK TO [<loginusername>]
GO
GRANT UPDATE TO [<loginusername>]
GO
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [<loginusername>]
GO
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [<loginusername>]
GO
GRANT VIEW ANY SENSITIVITY CLASSIFICATION TO [<loginusername>]
GO
GRANT VIEW DATABASE STATE TO [<loginusername>]
GO
GRANT VIEW DEFINITION TO [<loginusername>]
GO
ALTER ROLE [db_owner] ADD MEMBER <loginusername>;
GO
ALTER DATABASE [<saasdb>] MODIFY FILE ( NAME = N'<saasdb>', FILEGROWTH = 131072KB )
GO
ALTER LOGIN [<loginusername>] WITH DEFAULT_DATABASE=[<saasdb>], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO