Create Login/Role/User/Permission in SQL Server

USE master
GO

-- service logins
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NAM\EQRMS_RAZOR2')
        CREATE LOGIN [NAM\EQRMS_RAZOR2] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];
        
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NAM\EQRMS_RAZOR')
        CREATE LOGIN [NAM\EQRMS_RAZOR] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];
        
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'APAC\eqtgsybasehk')
        CREATE LOGIN [APAC\eqtgsybasehk] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];

--     
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NAM\GLBRazorDevDBO')
        CREATE LOGIN [NAM\GLBRazorDevDBO] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];    
            
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NAM\EQRZGLBDEV')
        CREATE LOGIN [NAM\EQRZGLBDEV] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];
        
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NAM\EQRZGLBProdSupport')
        CREATE LOGIN [NAM\EQRZGLBProdSupport] FROM WINDOWS WITH DEFAULT_DATABASE=[Razor];
GO

    
-- use <insert database name here>
-- for example:
--USE [DivServiceNormalised]
--USE [DivServiceBloomberg]
--USE [DivServiceEQRMS]
--USE [MarkitEquities]
--USE [DivServiceReuters]
--USE [Razor]
--USE [YeildCurve]
--USE [YeildCurve_Hist]
GO

/*
*    Database roles:
*/

-- AppServices
-- all environments
IF EXISTS(SELECT 1 FROM sysusers WHERE issqlrole = 1 AND [name] = 'AppServices')
    drop role AppServices    
GO
CREATE ROLE AppServices AUTHORIZATION [dbo]
go

GRANT DELETE, INSERT, UPDATE, SELECT, EXECUTE TO AppServices
GO
EXEC sp_addrolemember 'db_ddladmin', 'AppServices';
GO

-- Developers
IF EXISTS(SELECT 1 FROM sysusers WHERE issqlrole = 1 AND [name] = 'Developers')
    drop role Developers    
GO
CREATE ROLE Developers AUTHORIZATION [dbo]
go
-- all env
GRANT SELECT TO Developers
GO
GRANT VIEW DEFINITION TO Developers
GO
GRANT SHOWPLAN TO Developers
GO
-- Developers DEV/IST, QA, UAT all of the above +
GRANT DELETE, INSERT, UPDATE, SELECT, EXECUTE TO Developers WITH GRANT OPTION
GO
-- Developers DEV/IST only all of the above +
EXEC sp_addrolemember 'db_ddladmin', 'Developers';
GO
GRANT ALTER TRACE TO Developers
GO

-- Support
-- QA, UAT, PROD env
-- could be created in DEV/IST as well but would serve no purpose ...
IF EXISTS(SELECT 1 FROM sysusers WHERE issqlrole = 1 AND [name] = 'Support')
    drop role Support    
GO
CREATE ROLE Support AUTHORIZATION [dbo]
go
GRANT DELETE, INSERT, UPDATE, SELECT, EXECUTE TO Support
GO
GRANT VIEW DEFINITION TO Support
GO
GRANT SHOWPLAN TO Support
GO
GRANT ALTER TRACE TO Support
GO

-- RazorDevDBA
-- DEV/IST, QA, UAT
IF EXISTS(SELECT 1 FROM sysusers WHERE issqlrole = 1 AND [name] = 'RazorDevDBA')
    drop role RazorDevDBA    
GO
CREATE ROLE RazorDevDBA AUTHORIZATION [dbo]
go

EXEC sp_addrolemember 'db_owner', 'RazorDevDBA';
GO
GRANT ALTER TRACE TO RazorDevDBA
GO
GRANT VIEW DATABASE STATE to RazorDevDBA
go
GRANT VIEW SERVER STATE to RazorDevDBA
go

----------------------------------------------------------------------------
-- Add Users To Database
CREATE USER [EUR\ldneqtsvcrazor01] FOR LOGIN [EUR\ldneqtsvcrazor01]
GO
CREATE USER [NAM\EQRMS_RAZOR2] FOR LOGIN [NAM\EQRMS_RAZOR2]
GO
CREATE USER [NAM\EQRMS_RAZOR] FOR LOGIN [NAM\EQRMS_RAZOR]
GO
CREATE USER [APAC\eqtgsybasehk] FOR LOGIN [APAC\eqtgsybasehk]
GO
CREATE USER [NAM\GLBRazorDevDBO] FOR LOGIN [NAM\GLBRazorDevDBO]
GO
CREATE USER [NAM\EQRZGLBDEV] FOR LOGIN [NAM\EQRZGLBDEV]
GO
CREATE USER [NAM\EQRZGLBProdSupport] FOR LOGIN [NAM\EQRZGLBProdSupport]
GO


-- Add Users to Roles
EXEC sp_addrolemember N'RazorDevDBA', N'NAM\GLBRazorDevDBO'
GO
EXEC sp_addrolemember N'AppServices', N'EUR\ldneqtsvcrazor01'
GO
EXEC sp_addrolemember N'AppServices', N'NAM\EQRMS_RAZOR2'
GO
EXEC sp_addrolemember N'AppServices', N'NAM\EQRMS_RAZOR'
GO
EXEC sp_addrolemember N'AppServices', N'APAC\eqtgsybasehk'
GO
EXEC sp_addrolemember N'Developers', N'NAM\EQRZGLBDEV'
GO
EXEC sp_addrolemember N'Support', N'NAM\EQRZGLBProdSupport'
GO