USE master; GO
--CRIANDO UM "SERVER ROLE" AUTORIZADO POR "sa" CREATE SERVER ROLE applications AUTHORIZATION sa; GO
--CRIANDO UM "LOGIN" PARA ESTA INSTÂNCIA SQL CREATE LOGIN [durval.ramos] WITH PASSWORD = '!@#456S'; GO
--ADICIONANDO O NOVO LOGIN SQL PARA O "SERVER ROLE" CHAMADO "applications" ALTER SERVER ROLE applications ADD MEMBER [durval.ramos]; GO
USE DBSecurity; GO
--CRIANDO UM "USER" E VINCULANDO AO "LOGIN" (COM MESMO NOME) CREATE USER [durval.ramos] FOR LOGIN [durval.ramos] WITH DEFAULT_SCHEMA=dbo; GO
--CRIANDO UM "DATABASE ROLE" AUTORIZADO POR "dbo" CHAMADO "db_app" CREATE ROLE db_app AUTHORIZATION dbo; GO
--ADICIONANDO O "USER" COMO MEMBRO DO DATABASE ROLE "db_app" ALTER ROLE db_app ADD MEMBER [durval.ramos]; GO
--ADICIONANDO O DATABASE ROLE "db_app" COMO MEMBRO --DOS DATABASE ROLE "db_datareader" E "db_datawriter" ALTER ROLE db_datareader ADD MEMBER db_app; GO
ALTER ROLE db_datawriter ADD MEMBER db_app; GO
USE master GO
--CONCEDENDO PERMISSÕES PARA ESTA INSTANCIA AO LOGIN "durval.ramos" GRANT ALTER ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION, CONNECT ANY DATABASE, AUTHENTICATE SERVER, VIEW SERVER STATE, SELECT ALL USER SECURABLES TO [durval.ramos]; GO
--RESTRINGINDO PERMISSÕES PARA ESTA INSTANCIA AO LOGIN "durval.ramos" DENY ADMINISTER BULK OPERATIONS, ALTER ANY LINKED SERVER, ALTER ANY LOGIN, CREATE ANY DATABASE, CREATE SERVER ROLE TO [durval.ramos]; GO
DENY VIEW ANY DATABASE TO [durval.ramos] GO
DENY VIEW ANY DEFINITION, VIEW SERVER STATE TO [durval.ramos] GO
GRANT ALTER, ALTER ANY ASSEMBLY, AUTHENTICATE, CONNECT, CREATE AGGREGATE, CREATE ASSEMBLY, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, SHOWPLAN, UPDATE, VIEW DATABASE STATE, VIEW DEFINITION TO [durval.ramos]; GO
DENY ALTER ANY APPLICATION ROLE, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY ROLE, ALTER ANY SCHEMA, ALTER ANY SYMMETRIC KEY, ALTER ANY USER, CHECKPOINT, CREATE ASYMMETRIC KEY, CREATE CERTIFICATE, CREATE CONTRACT, CREATE ROLE, CREATE SYMMETRIC KEY TO [durval.ramos]; GO
USE DBSecurity GO
DENY VIEW DATABASE STATE, VIEW DEFINITION TO [db_app] AS [dbo] GO
DENY IMPERSONATE ANY LOGIN TO [durval.ramos]; GO