Horizon View Events 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 [HZNViewEvents] ON PRIMARY

(NAME = N'HZNViewEvents', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\HZNViewEvents.mdf', SIZE = 250MB, FILEGROWTH = 10% )

LOG ON

(NAME = N'HZNViewEvents_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\HZNViewEvents.ldf', SIZE = 100MB, FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

go

 

 

use HZNViewEvents

go

CREATE LOGIN [hznveventuser] WITH PASSWORD=N'!2345AbcDE', DEFAULT_DATABASE=HZNViewEvents, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF

go

CREATE USER [hznveventuser] for LOGIN [hznveventuser]

go

use MSDB

go

CREATE USER [hznveventuser] for LOGIN [hznveventuser]

go

 

 

use HZNViewEvents

go

sp_addrolemember @rolename = 'db_owner', @membername = 'hznveventuser'

go

use MSDB

go

sp_addrolemember @rolename = 'db_owner', @membername = 'hznveventuser'

go

 

 

CREATE SCHEMA [HZNVEVENTS]

go

ALTER USER [hznveventuser] WITH DEFAULT_SCHEMA =[HZNVEVENTS_ADMIN_ROLE]

 

if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVEVENTS_ADMIN_ROLE')

CREATE ROLE HZNVEVENTS_ADMIN_ROLE;

GRANT ALTER ON SCHEMA :: [HZNVEVENTS] to HZNVEVENTS_ADMIN_ROLE;

GRANT REFERENCES ON SCHEMA :: [HZNVEVENTS] to HZNVEVENTS_ADMIN_ROLE;

GRANT INSERT ON SCHEMA :: [HZNVEVENTS] to HZNVEVENTS_ADMIN_ROLE;

 

GRANT CREATE TABLE to HZNVEVENTS_ADMIN_ROLE;

GRANT CREATE VIEW to HZNVEVENTS_ADMIN_ROLE;

GRANT CREATE Procedure to HZNVEVENTS_ADMIN_ROLE;

 

if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVEVENTS_USER_ROLE')

CREATE ROLE HZNVEVENTS_USER_ROLE

go

GRANT SELECT ON SCHEMA ::  [HZNVEVENTS] to HZNVEVENTS_USER_ROLE

go

GRANT INSERT ON SCHEMA ::  [HZNVEVENTS] to HZNVEVENTS_USER_ROLE

go

GRANT DELETE ON SCHEMA ::  [HZNVEVENTS] to HZNVEVENTS_USER_ROLE

go

GRANT UPDATE ON SCHEMA ::  [HZNVEVENTS] to HZNVEVENTS_USER_ROLE

go

GRANT EXECUTE ON SCHEMA :: [HZNVEVENTS] to HZNVEVENTS_USER_ROLE

go

sp_addrolemember HZNVEVENTS_USER_ROLE , [hznveventuser]

go

sp_addrolemember HZNVEVENTS_ADMIN_ROLE , [hznveventuser]

go

use MSDB

go

if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'HZNVEVENTS_ADMIN_ROLE')

CREATE ROLE HZNVEVENTS_ADMIN_ROLE;

go

GRANT SELECT on msdb.dbo.syscategories to HZNVEVENTS_ADMIN_ROLE

go

GRANT SELECT on msdb.dbo.sysjobsteps to HZNVEVENTS_ADMIN_ROLE

go

GRANT SELECT ON msdb.dbo.sysjobs to HZNVEVENTS_ADMIN_ROLE

go

GRANT SELECT ON msdb.dbo.sysjobs_view to HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_add_job TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_delete_job TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_update_job TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO HZNVEVENTS_ADMIN_ROLE

go

GRANT EXECUTE ON msdb.dbo.sp_add_category TO HZNVEVENTS_ADMIN_ROLE

go

sp_addrolemember HZNVEVENTS_ADMIN_ROLE , [hznveventuser]

go

use master

go

grant VIEW SERVER STATE to [hznveventuser]

go

GRANT VIEW ANY DEFINITION TO [hznveventuser]

go
Posted in View, Vmware

Leave a Reply

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

*