Horizon View Composer Database Creation Script

I built this script out to create a Horizon View Composer database along with the schemas and permissions for my lab.

use [master]
 go
 CREATE DATABASE [HZNViewComposer] ON PRIMARY
 (NAME = N'HZNViewComposer', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\HZNViewComposer.mdf', SIZE = 250MB, FILEGROWTH = 10% )
 LOG ON
 (NAME = N'HZNViewComposer_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\HZNViewComposer.ldf', SIZE = 100MB, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
 go
 use HZNViewComposer
 go
 CREATE LOGIN [hznvcmpuser] WITH PASSWORD=N'P@ssword', DEFAULT_DATABASE=HZNViewComposer, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF
 go
 CREATE USER [hznvcmpuser] for LOGIN [hznvcmpuser]
 go
 use MSDB
 go
 CREATE USER [hznvcmpuser] for LOGIN [hznvcmpuser]
 go
 use HZNViewComposer
 go
 sp_addrolemember @rolename = 'db_owner', @membername = 'hznvcmpuser'
 go
 use MSDB
 go
 sp_addrolemember @rolename = 'db_owner', @membername = 'hznvcmpuser'
 go
 CREATE SCHEMA [HZNVCMP]
 go
 ALTER USER [hznvcmpuser] WITH DEFAULT_SCHEMA =[HZNVCMP_ADMIN_ROLE]</pre>
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVCMP_ADMIN_ROLE')
CREATE ROLE HZNVCMP_ADMIN_ROLE;
GRANT ALTER ON SCHEMA :: [HZNVCMP] to HZNVCMP_ADMIN_ROLE;
GRANT REFERENCES ON SCHEMA :: [HZNVCMP] to HZNVCMP_ADMIN_ROLE;
GRANT INSERT ON SCHEMA :: [HZNVCMP] to HZNVCMP_ADMIN_ROLE;

GRANT CREATE TABLE to HZNVCMP_ADMIN_ROLE;
GRANT CREATE VIEW to HZNVCMP_ADMIN_ROLE;
GRANT CREATE Procedure to HZNVCMP_ADMIN_ROLE;

if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVCMP_USER_ROLE')
CREATE ROLE HZNVCMP_USER_ROLE
go
GRANT SELECT ON SCHEMA :: [HZNVCMP] to HZNVCMP_USER_ROLE
go
GRANT INSERT ON SCHEMA :: [HZNVCMP] to HZNVCMP_USER_ROLE
go
GRANT DELETE ON SCHEMA :: [HZNVCMP] to HZNVCMP_USER_ROLE
go
GRANT UPDATE ON SCHEMA :: [HZNVCMP] to HZNVCMP_USER_ROLE
go
GRANT EXECUTE ON SCHEMA :: [HZNVCMP] to HZNVCMP_USER_ROLE
go
sp_addrolemember HZNVCMP_USER_ROLE , [HZNVCMPuser]
go
sp_addrolemember HZNVCMP_ADMIN_ROLE , [HZNVCMPuser]
go
use MSDB
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVCMP_ADMIN_ROLE')
CREATE ROLE HZNVCMP_ADMIN_ROLE;
go
GRANT SELECT on msdb.dbo.syscategories to HZNVCMP_ADMIN_ROLE
go
GRANT SELECT on msdb.dbo.sysjobsteps to HZNVCMP_ADMIN_ROLE
go
GRANT SELECT ON msdb.dbo.sysjobs to HZNVCMP_ADMIN_ROLE
go
GRANT SELECT ON msdb.dbo.sysjobs_view to HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_job TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_update_job TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO HZNVCMP_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_category TO HZNVCMP_ADMIN_ROLE
go
sp_addrolemember HZNVCMP_ADMIN_ROLE , [hznvcmpuser]
go
use master
go
grant VIEW SERVER STATE to [hznvcmpuser]
go
GRANT VIEW ANY DEFINITION TO [hznvcmpuser]
go
Posted in View, Vmware

Leave a Reply

Your email address will not be published. Required fields are marked *

*