Setup vCentre DB & Schema...

vcentre_setup_db.sql

/*
Change DB name, location, and vpxuser password to whatever you wish.
Ed Morgan [ed.morgan@ansgroup.co.uk] - 16/01/2013
*/

use [master]
go
CREATE DATABASE [vCentre_Server] ON PRIMARY
(NAME = N'vCentre_Server', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\vCentre_Server.mdf' , SIZE = 3000KB , FILEGROWTH = 10% )
LOG ON
(NAME = N'vCentre_Server_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\vCentre_Server_log.ldf' , SIZE = 1000KB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use vCentre_Server
go
sp_addlogin @loginame=[vpxuser], @passwd=N'vpxuser', @defdb='vCentre_Server', @deflanguage='us_english'
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
CREATE SCHEMA [VMW] 
go
ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'VC_ADMIN_ROLE')
CREATE ROLE VC_ADMIN_ROLE;
GRANT ALTER ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT REFERENCES ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT INSERT ON SCHEMA ::  [VMW] to VC_ADMIN_ROLE;
GRANT CREATE TABLE to VC_ADMIN_ROLE;
GRANT CREATE VIEW to VC_ADMIN_ROLE;
GRANT CREATE Procedure to VC_ADMIN_ROLE;
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'VC_USER_ROLE')
CREATE ROLE VC_USER_ROLE
go
GRANT SELECT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT INSERT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT DELETE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT UPDATE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT EXECUTE ON SCHEMA :: [VMW] to VC_USER_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go
sp_addrolemember VC_USER_ROLE , [vpxuser]
go
use MSDB
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'VC_ADMIN_ROLE')
CREATE ROLE VC_ADMIN_ROLE;
go
grant select on msdb.dbo.syscategories to VC_ADMIN_ROLE
go
grant select on msdb.dbo.sysjobsteps to VC_ADMIN_ROLE
go
GRANT SELECT ON msdb.dbo.sysjobs to VC_ADMIN_ROLE
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_update_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_category TO VC_ADMIN_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go 

/*
Update Manager
*/

use [master]
go
CREATE DATABASE [VCUMDB] ON PRIMARY
(NAME = N'vcumdb', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCUMDB.mdf', SIZE = 4000KB, FILEGROWTH = 10%) LOG ON
(NAME = N'vcumdb_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCUMDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCUMDB
go
sp_addlogin @loginame=[vpxumuser], @passwd=N'password', @defdb='VCUMDB', @deflanguage='us_english'
go
ALTER LOGIN [vpxumuser] WITH CHECK_POLICY = OFF 
go
CREATE USER [vpxumuser] for LOGIN [vpxumuser]
go
use MSDB
go
CREATE USER [vpxumuser] for LOGIN [vpxumuser] 
go

/*
SRM DBs
*/

use [master]
go
CREATE DATABASE [SRMDB] ON PRIMARY
(NAME = N'srmdb', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\srmdb.mdf', SIZE = 4000KB, FILEGROWTH = 10%) LOG ON
(NAME = N'srmdb_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\srmdb.ldf', SIZE = 1000KB, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
go
use srmdb
go
sp_addlogin @loginame=[vpxsrmuser], @passwd=N’password’, @defdb='srmdb', @deflanguage='us_english'
go
ALTER LOGIN [vpxsrmuser] WITH CHECK_POLICY = OFF 
go
CREATE USER [vpxsrmuser] for LOGIN [vpxsrmuser]
go
CREATE SCHEMA [SRM] 
go
ALTER USER [vpxsrmuser] WITH DEFAULT_SCHEMA =[SRM]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go
use MSDB
go
CREATE USER [vpxsrmuser] for LOGIN [vpxsrmuser] 
go