Set SQL Server Database Permissions by Manually Creating Database Roles

By using this recommended method, the View Composer database administrator can set permissions for View Composer administrators to be granted through Microsoft SQL Server database roles.

VMware recommends this method because it removes the requirement to set up the db_owner role for View Composer administrators who install and upgrade View Composer.

In this procedure, you can provide your own names for the database login name, user name, and database roles. The user [vcmpuser] and database roles, VCMP_ADMIN_ROLE and VCMP_USER_ROLE, are example names. The dbo schema is created when you create the View Composer database. You must use the dbo schema name.

Prerequisites

• Verify that a View Composer database is created. See Add a View Composer Database to SQL Server.

Procedure

1. Log in to a Microsoft SQL Server Management Studio session as the sysadmin (SA) or a user account with sysadmin privileges.
2. Create a user who will be granted the appropriate SQL Server database permissions.

   use ViewComposer
   go
   CREATE LOGIN [vcmpuser] WITH PASSWORD=N'vcmpuser!0', DEFAULT_DATABASE=ViewComposer,
   DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF
   go
   CREATE USER [vcmpuser] for LOGIN [vcmpuser]
   go
   use MSDB
   go
   CREATE USER [vcmpuser] for LOGIN [vcmpuser] 
   go
   

3. In the View Composer database, create the database role VCMP_ADMIN_ROLE.
4. In the View Composer database, grant privileges to the VCMP_ADMIN_ROLE.
   1. Grant the schema permissions ALTER, REFERENCES, and INSERT on the dbo schema.
   2. Grant the permissions CREATE TABLE, CREATE VIEW, and CREATE PROCEDURES.
5. In the View Composer database, create the VCMP_USER_ROLE.
6. In the View Composer database, grant the schema permissions SELECT, INSERT, DELETE, UPDATE, and EXECUTE on the dbo schema to the VCMP_USER_ROLE.
7. Grant the VCMP_USER_ROLE to the user [vcmpuser].
8. Grant the VCMP_ADMIN_ROLE to the user [vcmpuser].
9. In the MSDB database, create the database role VCMP_ADMIN_ROLE.
10. Grant privileges to the VCMP_ADMIN_ROLE in MSDB.
    1. On the MSDB tables syscategories, sysjobsteps, and sysjobs, grant the SELECT permission to the user [vcmpuser].
    2. On the MSDB stored procedures sp_add_job, sp_delete_job, sp_add_jobstep, sp_update_job, sp_add_jobserver, sp_add_jobschedule, and sp_add_category, grant the EXECUTE permission to the role VCMP_ADMIN_ROLE.
11. In the MSDB database, grant the VCMP_ADMIN_ROLE to the user [vcmpuser].
12. Create the ODBC System DSN using the SQL Server login vcmpuser.
    For instructions, see Add an ODBC Data Source to SQL Server.
13. Install View Composer.
    For instructions, see Install the View Composer Service.
14. In the MSDB database, revoke the VCMP_ADMIN_ROLE from the user [vcmpuser].
    After you revoke the role, you can leave the role as inactive or remove the role for increased security.