Prepare the vCenter Server Database by Creating Custom Database Schema and Roles

As an alternative to using the db_owner database role, experienced database administrators can set permissions by creating database schema and roles manually, which ensures greater control over database permissions.

You must first create a database and user for vCenter Server. Then you can create a custom schema and new database roles for the database user. You must also enable database monitoring for the user before you install vCenter Server. See Database Permission Requirements for vCenter Server.

To perform the following procedure, you can either use the graphical user interface or run scripts. The vCenter Server installer package contains example scripts in the vCenter-Server\dbschema\DB_and_schema_creation_scripts_PostgreSQL.txt file.

Procedure

Create a database and user for vCenter Server.

In the master database, create a database for vCenter Server.
Create a database user for vCenter Server and map it to the vCenter Server and msdb databases.

For example, to create the database VCDB and user vpxuser, you can run the following script:

use master
go 
CREATE DATABASE VCDB ON PRIMARY 
(NAME = N'vcdb', FILENAME = N'C:\database_path\VCDB.mdf', SIZE = 10MB, FILEGROWTH = 10% ) 
LOG ON 
(NAME = N'vcdb_log', FILENAME = N'C:\database_path\VCDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%) 
COLLATE SQL_Latin1_General_CP1_CI_AS 
go
use VCDB 
go 
CREATE LOGIN vpxuser WITH PASSWORD=N'vpxuser!0', DEFAULT_DATABASE=VCDB, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF
go 
CREATE USER vpxuser for LOGIN vpxuser
go
use MSDB
go
CREATE USER vpxuser for LOGIN vpxuser
go

You now have a Microsoft SQL Server database that you can use with vCenter Server.

In the vCenter Server database, create a database schema and assign it to the vCenter Server database user.
For example, to create the schema VMW in VCDB and assign it to the vpxuser user, you can run the following script:
```
use VCDB
CREATE SCHEMA VMW
go
ALTER USER vpxuser WITH DEFAULT_SCHEMA =VMW
```

In the vCenter Server database, create and grant privileges to the VC_ADMIN_ROLE and VC_USER_ROLE database roles and assign them to the vCenter Server database user.

For example, to create the roles in VCDB and assign them to the vpxuser user, you can run the following script:

use VCDB
go
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

In the msdb database, create and grant privileges to the VC_ADMIN_ROLE database role and assign it to the vCenter Server database user.

For example, to create the roles and assign them to the vpxuser user, you can run the following script:

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

Note: The VC_ADMIN_ROLE role in the msdb database is required only during installation and upgrade of vCenter Server. After the installation or upgrade, you can revoke the role and leave it as inactive for future upgrades, or you can remove it for increased security.

Enable database monitoring for the vCenter Server database user.
For example, to grant database disk size monitoring permissions to the vpxuser user, you can run the following script:
```
use master
go
grant VIEW SERVER STATE to vpxuser
go
GRANT VIEW ANY DEFINITION TO vpxuser
go
```