// Parker Street, main office where maintenance desk is

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO
/****** Object:  Database [Maintenance]    Script Date: 10/26/2005 11:19:17 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Maintenance')
DROP DATABASE [Maintenance]
GO
/****** Object:  Login [Inspector]    Script Date: 10/26/2005 11:19:49 ******/

CREATE DATABASE Maintenance
Go
ALTER DATABASE Maintenance
SET ENABLE_BROKER
GO
ALTER DATABASE Maintenance
SET RECOVERY SIMPLE

// must be trustworthy to communicate with others
ALTER DATABASE Maintenance
SET TRUSTWORTHY ON


-- needs cert in master to be able to authenticate by cert
CREATE CERTIFICATE [Parker Street]
WITH SUBJECT = 'Parker Street'


-- save the public version for others to use
BACKUP CERTIFICATE [Parker Street] 
TO FILE = 'C:\ParkerStreet.crt'

-- need endpoint based on cert auth
CREATE ENDPOINT [Parker Street EP]
STATE = STARTED
AS TCP (LISTENER_PORT = 5700)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [Parker Street])



-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


-- need login and user to work with Bld5

CREATE LOGIN [Bld5] WITH PASSWORD = 'P@ssw0rd'
CREATE USER [Bld5]

CREATE CERTIFICATE [Bld5PubCert] AUTHORIZATION [Bld5]
FROM FILE = 'C:\Bld5.crt'

GRANT CONNECT ON ENDPOINT::[Parker Street EP]
TO Bld5


-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

use [Maintenance]

CREATE 
--DROP
USER [Bld5]

CREATE 
--DROP
CERTIFICATE [Bld5PubCert] 
AUTHORIZATION [Bld5]
FROM FILE = 'C:\Bld5.crt'



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

CREATE QUEUE [Central Maintenance Queue]

CREATE MESSAGE TYPE MsgT1 VALIDATION = NONE
CREATE CONTRACT Con1 (MsgT1 SENT BY ANY)



CREATE 
--DROP
SERVICE [Central Maintenance]
ON QUEUE [Central Maintenance Queue]
([DEFAULT])

CREATE ROUTE Inspection
WITH SERVICE_NAME = 'Inspection Team',
ADDRESS = N'TCP://Bld5:5700'

sp_addrolemember 'db_owner', 'Bld5'
--sp_droprolemember 'db_owner', 'Bld5'

CREATE USER Inspector
WITHOUT LOGIN

CREATE Certificate InspectorCert
Authorization Inspector
WITH SUBJECT = 'Inspector'

BACKUP CERTIFICATE InspectorCert
TO FILE = 'C:\InspectorPub.crt'

GRANT 
--REVOKE
SEND ON SERVICE::[Central Maintenance]
TO [Inspector]


GRANT 
--REVOKE
CONTROL ON SERVICE::[Central Maintenance]
TO [Bld5]

GRANT 
--REVOKE
CONTROL ON SERVICE::[Central Maintenance]
TO [guest]



GRANT 
--REVOKE
SEND ON SERVICE::[Central Maintenance]
TO [public]

use maintenance
GRANT 
--REVOKE
SEND ON SERVICE::[Central Maintenance]
TO Bld5




GRANT 
--REVOKE
CONTROL ON CONTRACT::[DEFAULT]
TO [public]

GRANT 
--REVOKE
CONTROL ON MESSAGE TYPE::[DEFAULT]
TO [public]


use msdb
CREATE 
--DROP
USER [Bld5]


select * from sys.databases



select * from sys.sysusers


select * from sys.databases

use msdb
Create 
--Drop
Route Maintenance
WITH SERVICE_NAME = 'Central Maintenance',
BROKER_INSTANCE='6D04E932-630D-4C1A-ADB1-B52FFAFAB576',
ADDRESS=N'LOCAL'

Create Route Maintenance1
WITH SERVICE_NAME = 'Central Maintenance',
--BROKER_INSTANCE='6D04E932-630D-4C1A-ADB1-B52FFAFAB576',
ADDRESS=N'LOCAL'



-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

use [Maintenance]

select CAST(message_body AS XML) from [Central Maintenance Queue]
select * from [Central Maintenance Queue]


CREATE 
--DROP
QUEUE [Inspection Queue]

CREATE 
--DROP
SERVICE [Inspection Team]
ON QUEUE [Inspection Queue]
([DEFAULT])



EXEC SendMessage '<test/>'

select * from [Inspection Queue]
select * from sys.transmission_queue


-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


CREATE LOGIN [ScottBldSB] WITH PASSWORD = 'P@ssw0rd'

--DROP USER [ScottBldSB]
--DROP USER [Bld5SB]
use master
CREATE USER [ScottBldSB]
CREATE CERTIFICATE [Bld5Cert] AUTHORIZATION [Bld5SB]
FROM FILE = 'C:\ParkerStreet\Bld5.crt'

CREATE CERTIFICATE [ScottBldCert] AUTHORIZATION [ScottBldSB]
FROM FILE = 'C:\ParkerStreet\ScottBld.crt'


use msdb
CREATE USER [ScottBldSB]
CREATE USER [Bld5SB]
CREATE CERTIFICATE [Bld5Cert] AUTHORIZATION [Bld5SB]
FROM FILE = 'C:\ParkerStreet\Bld5.crt'

CREATE CERTIFICATE [ScottBldCert] AUTHORIZATION [ScottBldSB]
FROM FILE = 'C:\ParkerStreet\ScottBld.crt'



use [Maintenance]

CREATE USER [ScottBldSB]
CREATE USER [Bld5SB]

CREATE CERTIFICATE [Bld5Cert] AUTHORIZATION [Bld5SB]
FROM FILE = 'C:\ParkerStreet\Bld5.crt'

CREATE CERTIFICATE [ScottBldCert] AUTHORIZATION [ScottBldSB]
FROM FILE = 'C:\ParkerStreet\ScottBld.crt'



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'


CREATE CERTIFICATE [Bld5Cert] AUTHORIZATION [Bld5SB]
FROM FILE = 'C:\ParkerStreet\Bld5.crt'

CREATE CERTIFICATE [ScottBldCert] AUTHORIZATION [ScottBldSB]
FROM FILE = 'C:\ParkerStreet\ScottBld.crt'

USE master


GRANT CONNECT ON ENDPOINT::[Parker Street EP]
TO ScottBldSB





