Migrate objects from DBO schema to custom schema and add new roles instead of db_owner in MSDB and VCDB

Products

• VMware vCenter Server

Issue/Introduction

You can migrate all database objects in a DBO schema to a custom schema, if the following conditions are met:

• You are the database administrator (DBO) and you want to remove the DBO role.
• Your machine is running vCenter Server 5.5 or earlier.
• Your machine is running vCenter Server 4.x or vCenter Server 5.x.
• You are running Microsoft SQL Server.

During the vCenter Server installation, you can add new database roles to remove a vCenter Server user as the owner of the msdb database and then grant the vCenter Server database administrator role or vCenter Server user role for accessing, installing, or upgrading the vCenter Server database. The new vCenter Server database roles enable any vCenter Server user to become the owner of the vCenter Server database.

Environment

VMware vCenter Server 5.5.x
VMware vCenter Server 4.1.x
VMware vCenter Server 5.1.x
VMware vCenter Server 4.0.x
VMware vCenter Server 5.0.x

Resolution

Use the following instructions to migrate the DBO schema objects to a custom schema.

Prerequisites

• You must be the owner of the vCenter Server database (VCDB) or a user with database administrator permissions.
• You must have the CREATE USER ROLES permission in the msdb database.

Procedure

1. Download the Upgrade-Remove-DBO-Role.zip file, which is attached to this KB article.
   
   
2. Extract the files to a temporary folder in the machine where the Microsoft SQL Server is running.
   
   The Upgrade-Remove-DBO-Role.zip file contains the Upgrade-Remove-DBO-Rolemigration_checks_param.bat file and Upgrade-Remove-DBO-Rolemigration_exec_parazm.bat file.
   
   
3. Back up your current vCenter Server database (VCDB).
   
   
4. Stop the VMware VirtualCenter Server service.
   1. Click Start > Programs > Administrative Tools > Services.
   2. Right-click VMware VirtualCenter Server and click Stop.
      
      
5. Disconnect all connections to the vCenter Server database.
   
   
6. Edit the Upgrade-Remove-DBO-Rolemigration_checks_param.bat file.
   This script provides information on the objects that might be modified in the migration, lists schema binding views that have dbo hard coded, and also checks connections to the VCDB.
   
   
   

   Parameter	Description
   SERVERINSTANCE	The name of the server instance (host name).
   VCDB	The vCenter Server database name.
   VCUSER	The user who is the DB owner (DBO). Scripts are run using the user credentials of the DBO. This user must have privileges to create schema, grant roles and permissions, and add database roles in msdb. This user is not the vCenter Server user (vcadmin or vpxuser).
   PASS	The password of the VCUSER.
   VCenterUSER	The vCenter Server database user. The transfer or migration script assigns the roles and privileges to this user.
   CRSCHEMA	The name of the current VCDB schema. By default, the name is DBO. Note: Tables, views, and procedures are transferred to the new schema only if CRSCHEMA is named dbo. If CRSCHEMA is not dbo, the script adds database roles and grants or revokes permissions to the vCenter user.
   NEWSCHEMA	The name of the new schema.

   
   
7. Run the Upgrade-Remove-DBO-Rolemigration_checks_param.bat command to verify that the migration to a new schema is possible on your machine.
   Read the output of the script. If the command prompt shows an error message similar to the following, verify the permissions of the user who runs the script.
   Can not select from sys.databases
   You must run the script again, after correcting the parameter values.
   
   
8. Edit Upgrade-Remove-DBO-Rolemigration_exec_param.bat file.
   
   

   Parameter	Description
   SERVERINSTANCE	The name of the server instance (host name).
   VCDB	Thge vCenter Server database name.
   VCUSER	The user who is the DB owner (DBO). Scripts are run using the user credentials of the DBO. This user must have privileges to create schema, grant roles and permissions, and add database roles in msdb. This user is not the vCenter Server user (vcadmin or vpxuser).
   PASS	Thge password of the VCUSER.
   VCenterUSER	The vCenter Server database user. The transfer or migration script assigns the roles and privileges to this user.
   CRSCHEMA	The name of the current VCDB schema. By default, the name is DBO. Note: Tables, views, and procedures are transferred to the new schema only if CRSCHEMA is named dbo. If CRSCHEMA is not dbo, the script adds database roles and grants or revokes permissions to the vCenter user.
   NEWSCHEMA	The name of the new schema.

   
   Note: If any of these parameters are not set, the transfer script stops.
   
   
9. Run the Upgrade-Remove-DBO-Rolemigration_exec_param.bat script.
   This script creates two new roles in VCDB: VC_ADMIN_ROLE and VC_USER_ROLE
   
   The following permissions are granted to the VC_ADMIN_ROLE in the new schema:
   GRANT ALTER ON SCHEMA
   GRANT REFERENCES ON SCHEMA
   GRANT INSERT ON SCHEMA
   GRANT CREATE TABLE
   GRANT CREATE VIEW
   GRANT CREATE PROCEDURE
   
   The following permissions are granted to the VC_USER_ROLE in the new schema:
   GRANT SELECT ON SCHEMA
   GRANT INSERT ON SCHEMA
   GRANT DELETE ON SCHEMA
   GRANT UPDATE ON SCHEMA
   GRANT EXECUTE ON SCHEMA
   
   
10. Run a query similar to the following to verify that the schema objects are moved to the custom (VMW) schema:
    select t.name from sys.tables t, sys.schemas s where t.schema_id=s.schema_id and s.name='VMW' and t.type='U'

You can also revoke the DB owner role from the vCenter Server user and grant the DB owner privileges to
VC_ADMIN_ROLE or VC_USER_ROLE for install or upgrade operations and to
VC_USER_ROLE for accessing the vCenter Server.

An administrator role (VC_ADMIN_ROLE) is created in msdb. The following permissions are granted to VC_ADMIN_ROLE:
select on msdb.dbo.syscategories
select on msdb.dbo. sysjobsteps
select on msdb.dbo.sysjobs
execute on msdb.dbo.sp_add_job
execute on msdb.dbo.sp_delete_job
execute on msdb.dbo.sp_add_jobstep
execute on msdb.dbo.sp_update_job
execute on msdb.dbo.sp_add_jobserver
execute on msdb.dbo.sp_add_jobschedule
execute on msdb.dbo.sp_add_category

For installation or upgrade operations, you can grant the VC_ADMIN_ROLE to the vCenter user instead of the DB owner in msdb.