Use a Script to Create and Apply a Microsoft SQL Server Database Schema and Roles

In this method of configuring the SQL database, you create the custom schema VMW, instead of using the existing dbo schema. You must also enable Database Monitoring for a user before you install vCenter Server with an embedded or external Platform Services Controller.

This method requires that you create new database roles and grant them to the database user.

Prerequisites

To make sure you have the proper roles and permissions before upgrading vCenter Server, update the SQL Server database and users for vCenter Server.

Procedure

1. Log in to a Microsoft SQL Server Management Studio session as the sysadmin or a user account with sysadmin privileges.
2. Run the following script to create roles and apply privileges.
   The script is located in the vCenter Server installation package at /installation directory/vCenter-Server/dbschema/DB_and_schema_creation_scripts_MSSQL.txt .

   CREATE SCHEMA [VMW]
   go
   ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]
   
   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_USER_ROLE , [vpxuser]
   go
   sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
   go
   use MSDB
   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 SELECT ON msdb.dbo.sysjobs_view 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
   use master
   go
   grant VIEW SERVER STATE to [vpxuser]
   go
   GRANT VIEW ANY DEFINITION TO [vpxuser]
   go