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.

Before you begin

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

About this task

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

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