SQL Server set up logins, users and roles for CQRS

configure_cqrs_permissions.sql

-- 
-- http://dba.stackexchange.com/questions/2572/how-do-i-assign-an-entire-active-directory-group-security-access-in-sql-server-2
--

use master;
CREATE LOGIN [MyDomain\MyCoolApp Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[MyCoolApp];
GO

USE [MyCoolApp];
GO

-- Client application
CREATE USER [Administrators user] FROM LOGIN [MyDomain\MyCoolApp Administrators];

-- Service
-- in this example, we just let it run as local service to make it simple 
CREATE USER [MyCoolApp Service user] FROM LOGIN [NT AUTHORITY\LOCAL SERVICE];
GO

CREATE ROLE [Administrators role];
CREATE ROLE [MyCoolApp Service role];
GO

EXEC sp_addrolemember 'Administrators role', 'Administrators user';
EXEC sp_addrolemember 'MyCoolApp Service role', 'MyCoolApp Service user';
GO

-- The QUERY schema is open for read only
-- The admin can see everything:
GRANT SELECT ON SCHEMA :: [query] TO [Administrators role];

-- Only the service is allowed to write to the command store (and update the query model)
-- Open up for WRITE model to the MyCoolApp Service 
GRANT SELECT, INSERT ON SCHEMA :: [command] TO [MyCoolApp Service role];
GRANT SELECT, INSERT, UPDATE ON SCHEMA :: [query] TO [MyCoolApp Service role];

drop_login.sql

IF (Exists (select loginname from master.dbo.syslogins 
  		where name = 'MyDomain\MyCoolApp Administrators'))
	DROP LOGIN [MyDomain\MyCoolApp Administrators];