Alternative to Granting DBO Permissions to the vCenter Server Database User This document describes how to use optional scripts to create a Microsoft SQL database for vCenter Server and to create the database schema. If you do not use these scripts, you can create the database manually and allow the vCenter Server installer to create the database schema. To prepare a SQL Server database to work with vCenter Server, you generally need to create a SQL Server database user with database operator (DBO) rights. When you do this, you must make sure that the database user login has the db_owner fixed database role on the vCenter Server database and on the MSDB database. (The db_owner role on the MSDB database is required for installation and upgrade only. You can revoke this role after the installation or upgrade process is complete.) The purpose of granting DBO permissions to the vCenter Server database user is to enable the vCenter Server installer to create the vCenter Server database schema. For environments in which the user cannot have DBO permissions on the vCenter Server database, you can instead run scripts that create the vCenter Server database schema before you run the vCenter Server installer. You can use the following script to create a database, user, and permissions for successful installation of vCenter Server. Notes: 1) Change the location of the data and log files you would like to store in the create database script. 2) The user created by this script does not follow any security policy. The passwords are provided only for convenience. Please change the passwords as appropriate. 3) The user created will have DBO Privileges on both VCDB and and MSDB databases or will have VC_ADMIN_ROLE on both VCDB and MSDB and VC_USER_ROLE in VCDB. Logon to a Query Analyzer session with the sysadmin (SA) or a user account with sysadmin privileges and run the following script. ========================= use [master] go CREATE DATABASE [VCDB] ON PRIMARY (NAME = N'vcdb', FILENAME = N'C:\VCDB.mdf' , SIZE = 3000KB , FILEGROWTH = 10% ) LOG ON (NAME = N'vcdb_log', FILENAME = N'C:\VCDB.ldf' , SIZE = 1000KB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS go use VCDB go sp_addlogin @loginame=[vpxuser], @passwd=N'vpxuser', @defdb='VCDB', @deflanguage='us_english' go ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF go CREATE USER [vpxuser] for LOGIN [vpxuser] go CREATE SCHEMA [VMW] go ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW] go User should have DBO Privileges or VC_ADMIN_ROLE and VC_USER_ROLE database roles sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser' go or 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_ADMIN_ROLE , [vpxuser] go sp_addrolemember VC_USER_ROLE , [vpxuser] go use MSDB go CREATE USER [vpxuser] for LOGIN [vpxuser] go User should have DBO Privileges or VC_ADMIN_ROLE sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser' go or 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 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 ============= To create the vCenter Server database schema 1) Create the vCenter Server database. 2) Create a vCenter Server database user with the db_datawriter and db_datareader permissions. 3) Open a query analyzer window with a user having DBO rights on the vCenter Server and MSDB databases. 4) Locate the dbschema scripts in the vCenter Server installation package bin/dbschema directory. 5) Run the scripts in sequence on the database.The objects created by these scripts need to be owned by the "dbo" user. Open the scripts one at a time in the query analyzer window and press F5 to execute each script in the order shown here. VCDB_mssql.SQL VCDB_views_mssql.sql insert_stats_proc_mssql.sql load_stats_proc_mssql.sql purge_stat2_proc_mssql.sql purge_stat3_proc_mssql.sql purge_usage_stats_proc_mssql.sql stats_rollup1_proc_mssql.sql stats_rollup2_proc_mssql.sql stats_rollup3_proc_mssql.sql cleanup_events_mssql.sql delete_stats_proc_mssql.sql upsert_last_event_proc_mssql.sql load_usage_stats_proc_mssql.sql TopN_DB_mssql.sql calc_topn1_proc_mssql.sql calc_topn2_proc_mssql.sql calc_topn3_proc_mssql.sql calc_topn4_proc_mssql.sql clear_topn1_proc_mssql.sql clear_topn2_proc_mssql.sql clear_topn3_proc_mssql.sql clear_topn4_proc_mssql.sql rule_topn1_proc_mssql.sql rule_topn2_proc_mssql.sql rule_topn3_proc_mssql.sql rule_topn4_proc_mssql.sql process_license_snapshot_mssql.sql l_stats_rollup3_proc_mssql.sql l_purge_stat2_proc_mssql.sql l_purge_stat3_proc_mssql.sql l_stats_rollup1_proc_mssql.sql l_stats_rollup2_proc_mssql.sql process_performance_data_mssql.sql 6) Grant the execute privilege for all the store procedures you created in step 5 to the vCenter Server database user you created in step 2. grant execute on purge_stat2_proc to grant execute on purge_stat3_proc to grant execute on purge_usage_stat_proc to grant execute on stats_rollup1_proc to grant execute on stats_rollup2_proc to grant execute on stats_rollup3_proc to grant execute on cleanup_events_tasks_proc to grant execute on delete_stats_proc to grant execute on upsert_last_event_proc to grant execute on load_usage_stats_proc to grant execute on load_stats_proc to grant execute on insert_stats_proc to grant execute on calc_topn1_proc to grant execute on calc_topn2_proc to grant execute on calc_topn3_proc to grant execute on calc_topn4_proc to grant execute on clear_topn1_proc to grant execute on clear_topn2_proc to grant execute on clear_topn3_proc to grant execute on clear_topn4_proc to grant execute on rule_topn1_proc to grant execute on rule_topn2_proc to grant execute on rule_topn3_proc to grant execute on rule_topn4_proc to grant execute on process_license_snapshot_proc to grant execute on l_stats_rollup3_proc to grant execute on l_purge_stat2_proc to grant execute on l_purge_stat3_proc to grant execute on l_stats_rollup1_proc to grant execute on l_stats_rollup2_proc to grant execute on process_performance_data to 7) For all supported editions of Microsoft SQL Server (except Microsoft SQL Server 2005 Express), ensure that the SQL Agent is running. Run these additional scripts to set up scheduled jobs on the database. job_dbm_performance_data_mssql.sql job_schedule1_mssql.sql job_schedule2_mssql.sql job_schedule3_mssql.sql job_cleanup_events_mssql.sql job_topn_past_day_mssql.sql job_topn_past_week_mssql.sql job_topn_past_month_mssql.sql job_topn_past_year_mssql.sql job_property_bulletin_mssql.sql 8) On the machine on which you intend to install vCenter Server, create a data source name (DSN) that points to the database server with the vCenter Server schema. 9) Run the vCenter Server installer and, when prompted, provide the database user login.