Prepare the vCenter Server Database by Using the dbo Schema and the db_owner Database Role

The simplest way to assign permissions for a vCenter Server database user is through the database role db_owner.

You must first create a database and user for vCenter Server. Then you can use the existing dbo_owner database role and let the vCenter Server installer create the default dbo schema that assigns database user permissions to that role. 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

1. Create a database and user for vCenter Server.
   1. In the master database, create a database for vCenter Server.
   2. 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.
2. Assign the db_owner role to the vCenter Server database user on both the vCenter Server and msdb databases.
   For example, to assign the db_owner role to the vpxuser user, you can run the following script:

   use VCDB
   go
   sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
   go
   use MSDB
   go
   sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
   go

3. 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

Results

When you install vCenter Server, the installer uses the default dbo schema to assign permissions to the db_owner role.