使用脚本创建和应用 Microsoft SQL Server 数据库架构和角色

使用这种方法配置 SQL 数据库，可以创建自定义架构 VMW，而不是使用现有 dbo 架构。此外，安装具有嵌入式或外部 Platform Services Controller 的 vCenter Server 之前，您还必须为用户启用数据库监控。

这种方法要求您创建新的数据库角色，并将其授予给数据库用户。

前提条件

要确保在升级 vCenter Server 之前具有适当的角色和权限，请更新 vCenter Server 的 SQL Server 数据库和用户。

过程

1. 以 sysadmin 身份或具有 sysadmin 特权的用户帐户登录 Microsoft SQL Server Management Studio 会话。
2. 运行以下脚本以创建角色并应用特权。
   该脚本位于 vCenter Server 安装软件包的 /安装目录/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