184 lines
4.7 KiB
Markdown
184 lines
4.7 KiB
Markdown
# 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*.
|
|
|
|
|
|
```powershell
|
|
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
|
|
``` |