Search the VMware Knowledge Base (KB)
View by Article ID

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

  • 18 Ratings

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

 

Attachments

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 18 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 18 Ratings
Actions
KB: