-- Create Production Size Windchill Database
USE [master]
GO

-- Contained database authentication enable
exec sp_configure 'contained database authentication', 1
go
reconfigure
go

--  Create Database for Windchill
CREATE DATABASE [dbadmin120] CONTAINMENT = PARTIAL ON  PRIMARY
( NAME = N'dbadmin120', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\dbadmin120.mdf' , SIZE = 4000MB , FILEGROWTH = 10MB ),
 FILEGROUP [BLOBS] 
( NAME = N'dbadmin120_blobs', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\dbadmin120_blobs.ndf' , SIZE = 4000MB , FILEGROWTH = 10MB ), 
 FILEGROUP [INDX] 
( NAME = N'dbadmin120_indx', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\dbadmin120_indx.ndf' , SIZE = 4000MB , FILEGROWTH = 5MB ), 
 FILEGROUP [WCAUDIT] 
( NAME = N'dbadmin120_wcaudit', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\dbadmin120_wcaudit.ndf' , SIZE = 150MB , FILEGROWTH = 5MB )
 LOG ON 
( NAME = N'dbadmin120_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\dbadmin120_log.ldf' , SIZE = 100MB , MAXSIZE = 10000MB , FILEGROWTH = 10%) COLLATE Latin1_General_100_CS_AS_SC
GO
ALTER DATABASE [dbadmin120] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [dbadmin120] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [dbadmin120] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [dbadmin120] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [dbadmin120] SET ARITHABORT OFF 
GO
ALTER DATABASE [dbadmin120] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [dbadmin120] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [dbadmin120] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [dbadmin120] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [dbadmin120] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [dbadmin120] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [dbadmin120] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [dbadmin120] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [dbadmin120] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [dbadmin120] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [dbadmin120] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [dbadmin120] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [dbadmin120] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [dbadmin120] SET  READ_WRITE 
GO
ALTER DATABASE [dbadmin120] SET RECOVERY FULL 
GO
ALTER DATABASE [dbadmin120] SET  MULTI_USER 
GO
ALTER DATABASE [dbadmin120] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [dbadmin120] SET DISABLE_BROKER  
GO
ALTER DATABASE [dbadmin120] SET ALLOW_SNAPSHOT_ISOLATION OFF  
GO
ALTER DATABASE [dbadmin120] SET DB_CHAINING OFF  
GO
ALTER DATABASE [dbadmin120] SET READ_COMMITTED_SNAPSHOT ON
GO
USE [dbadmin120]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [dbadmin120] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

--  Create Database Role for Windchill
USE [dbadmin120]
GO
CREATE ROLE [db_windchill] AUTHORIZATION dbo
GO
GRANT CREATE FULLTEXT CATALOG TO [db_windchill]
GO
GRANT CREATE FUNCTION TO [db_windchill]
GO
GRANT CREATE PROCEDURE TO [db_windchill]
GO
GRANT CREATE SYNONYM TO [db_windchill]
GO
GRANT CREATE TABLE TO [db_windchill]
GO
GRANT CREATE TYPE TO [db_windchill]
GO
GRANT CREATE VIEW TO [db_windchill]
GO
GRANT DELETE TO [db_windchill]
GO
GRANT EXECUTE TO [db_windchill]
GO
GRANT INSERT TO [db_windchill]
GO
GRANT REFERENCES TO [db_windchill]
GO
GRANT SELECT TO [db_windchill]
GO
GRANT UPDATE TO [db_windchill]
GO
GRANT SHOWPLAN TO [db_windchill]
GO
CREATE SCHEMA [db_windchill] AUTHORIZATION [db_windchill]
GO
ALTER AUTHORIZATION ON ROLE::[db_windchill] TO [db_windchill]
GO

-- Create and Schema, user, login
USE [dbadmin120]
GO
CREATE SCHEMA [dbadmin120] AUTHORIZATION [dbo]
GO
CREATE LOGIN [dbadmin120] WITH PASSWORD=N'dbadmin120', DEFAULT_DATABASE=[dbadmin120], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [dbadmin120] ENABLE
GO
CREATE USER [dbadmin120] FOR LOGIN [dbadmin120]
GO
ALTER USER [dbadmin120] WITH DEFAULT_SCHEMA=[dbadmin120]
GO
EXEC sp_addrolemember N'db_windchill', N'dbadmin120'
GO
ALTER AUTHORIZATION ON SCHEMA::[dbadmin120] TO [dbadmin120]
GO