Custom Database Role for Horizon View Events Log

This post is based on something I am working on and since I found no other information on it, I had to figure it out. Now that I think I’ve got all the pieces in place, I’d like to share it too.

The request is simple, to avoid granting dbo rights to the database account. The task is to figure out the exact MS SQL database permissions required for View to operate the View Events database.

For View Composer, it’s simple as the Horizon View documentation already has a section on custom role for View Composer database access. If that is what you are looking for, it’s available here for Horizon 6.1.

For View Events, I believe the database operations are much simpler (compared to View Composer), and so it seems that a subset of the permissions (compared to View Composer) is required. True enough, I was right about this. Once I had the permissions in place, I was able to complete configuring the View Events database from the Horizon View admin portal. Otherwise, the operation will fail with an error.

The key permissions needed by the database account are the following

• ALTER
• CONNECT (typically already granted by default)
• CREATE TABLE
• CREATE VIEW
• DELETE
• EXECUTE
• INSERT
• SELECT
• UPDATE

Below is an example, do make the necessary changes to suit your implementation.

[table]Item,Specification,Remarks
Horizon View,6.1,this is what I tested with
MS SQL Server,2008 R2,this is what I tested with
SQL Database,ViewEventsDB,change as needed
SQL User Account,viewuser,change as needed
SQL Role,role_ViewUser,change as needed
[/table]

Below is the SQL script that I used, do customise it with to suit your needs. Prior to running the script, you will need to first create the database. With the database created, you can then run the script to create the account, role and grant the rights.

USE [master]
GO
CREATE LOGIN [viewuser] WITH PASSWORD=N'YourPasswordHere',DEFAULT_DATABASE=ViewEventsDB, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF
GO

USE [ViewEventsDB]
CREATE USER [viewuser] for LOGIN [viewuser];
CREATE ROLE [role_ViewUser] AUTHORIZATION [dbo];
EXEC sp_addrolemember [role_ViewUser], [viewuser];
GO

use [ViewEventsDB]
GRANT CREATE TABLE TO [role_ViewUser]
GRANT CREATE VIEW TO [role_ViewUser]
GRANT DELETE TO [role_ViewUser]
GRANT EXECUTE TO [role_ViewUser]
GRANT INSERT TO [role_ViewUser]
GRANT SELECT TO [role_ViewUser]
GRANT UPDATE TO [role_ViewUser]
GRANT ALTER TO [role_ViewUser]
GO