Loading

×Sorry to interrupt
CSS Error

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

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

Last Updated: 5/2/2016Categories: How toTotal Views: 7848Language:
English

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

Details

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.

Solution

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

Download the Upgrade-Remove-DBO-Role.zip file, which is attached to this KB article.

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.

Back up your current vCenter Server database (VCDB).

Stop the VMware VirtualCenter Server service.

Click Start > Programs > Administrative Tools > Services.

Right-click VMware VirtualCenter Server and click Stop.

Disconnect all connections to the vCenter Server database.

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.

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.

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.

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

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.

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
English

Attachments

Upgrade-Remove-DBO-Role.zip

DBO_VCDB_objects.zip

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

KB
•
从目录服务（OpenLDAP 或 Active Directory）添加用户组和各个用户后无法管理 vCenter Single Sign-On (2095342)

Results 1-2 of 2

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware vCenter Server

Related Versions:

VMware vCenter Server 5.5.x

VMware vCenter Server 5.1.x

VMware vCenter Server 5.0.x

VMware vCenter Server 4.1.x

VMware vCenter Server 4.0.x

Take Our Survey

Support Assistant