-- Description: Script de criação de banco de dados SQL Server. O Banco de dados Agro utiliza a collation Latin1_General_CI_AS.
CREATE DATABASE [{{DB_NAME}}]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'{{DB_NAME}}', FILENAME = N'{{DB_FILE_DATABASE}}{{DB_NAME}}.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'{{DB_NAME}}_log', FILENAME = N'{{DB_FILE_DATABASE_LOG}}{{DB_NAME}}_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 COLLATE Latin1_General_CI_AS
GO
ALTER DATABASE [{{DB_NAME}}] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [{{DB_NAME}}] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET ARITHABORT OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [{{DB_NAME}}] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [{{DB_NAME}}] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [{{DB_NAME}}] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET  DISABLE_BROKER 
GO
ALTER DATABASE [{{DB_NAME}}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [{{DB_NAME}}] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [{{DB_NAME}}] SET  READ_WRITE 
GO
ALTER DATABASE [{{DB_NAME}}] SET RECOVERY FULL 
GO
ALTER DATABASE [{{DB_NAME}}] SET  MULTI_USER 
GO
ALTER DATABASE [{{DB_NAME}}] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [{{DB_NAME}}] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [{{DB_NAME}}] SET DELAYED_DURABILITY = DISABLED 
GO
USE [{{DB_NAME}}]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [{{DB_NAME}}]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [{{DB_NAME}}] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

-- Criação do Usuário do {{DB_USUARIODB_USUARIO_PCONNECTOR}}
CREATE LOGIN {{DB_USUARIO_PCONNECTOR}} WITH PASSWORD = '{{DB_SENHA_PIMS_CONNECTOR}}';

-- Utilizando o Banco de Dados {{DB_NAME}}
USE [{{DB_NAME}}];
GO

-- Criar Usuário no Banco de Dados {{DB_USUARIO_PCONNECTOR}}
CREATE USER {{DB_USUARIO_PCONNECTOR}} FOR LOGIN {{DB_USUARIO_PCONNECTOR}};

-- Concedendo as Permissões ao Usuário {{DB_USUARIO_PCONNECTOR}}
GRANT SELECT, INSERT, UPDATE, DELETE TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE TABLE TO {{DB_USUARIO_PCONNECTOR}};
GRANT EXECUTE TO {{DB_USUARIO_PCONNECTOR}};
GRANT CONNECT TO {{DB_USUARIO_PCONNECTOR}};
GRANT ALTER TO {{DB_USUARIO_PCONNECTOR}};
GRANT VIEW DEFINITION TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE SYNONYM TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE PROCEDURE TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE VIEW TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE FUNCTION TO {{DB_USUARIO_PCONNECTOR}};
GRANT CREATE SCHEMA TO {{DB_USUARIO_PCONNECTOR}};



--Criação do Schema do {{DB_USUARIO_PCONNECTOR}}
USE [{{DB_NAME}}]
GO
CREATE SCHEMA [{{DB_USUARIO_PCONNECTOR}}] AUTHORIZATION [{{DB_USUARIO_PCONNECTOR}}]
GO


--Atribuição do Schema Default do {{DB_USUARIO_PCONNECTOR}}
USE [{{DB_NAME}}]
GO
ALTER USER [{{DB_USUARIO_MULTICULTIVO}}] WITH DEFAULT_SCHEMA=[{{DB_USUARIO_MULTICULTIVO}}]
GO


--Ajuste da Permissão do {{DB_USUARIO_MULTICULTIVO}} para acessar o Schema ssma_oracle
USE [{{DB_NAME}}]
GO
ALTER AUTHORIZATION ON SCHEMA::[ssma_oracle] TO [{{DB_USUARIO_MULTICULTIVO}}]