* Archive * Admin

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
2015.11.09 vCenter 5.5 SQL文

------------------------------------------------------------
◆ SQL データベースの作成
------------------------------------------------------------

インストールCD内のテンプレートを修正

DVDドライブ:\vCenter-Server\dbschema\DB_and_schema_creation_scripts_MSSQL.txt

修正箇所:

○ データベースサイズ変更
SIZE = 3000KB → SIZE = 5000KB

○ ファイルのパスを変更
C:\VCDB.mdf
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VCDB.mdf

C:\VCDB.ldf
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VCDB.ldf


○ データベースの復旧モデルを 単純 に設定(追加)
========================================================
ALTER DATABASE [VCDB] SET RECOVERY SIMPLE

go
========================================================

○ パスワード変更
@passwd=N'vpxuser',
@passwd=N'Password@1',


○ コメントアウト(/* ~ */を追加)
========================================================

/*
User should have DBO Privileges or VC_ADMIN_ROLE and VC_USER_ROLE database roles

sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go

or
*/

(省略)

/*
User should have DBO Privileges or VC_ADMIN_ROLE

sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go

or
*/

========================================================


例文:
========================================================
use [master]

go

CREATE DATABASE [VCDB] ON PRIMARY

(NAME = N'VCDB', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VCDB.mdf' , SIZE = 5000KB , FILEGROWTH = 10% )

LOG ON

(NAME = N'VCDB_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VCDB.ldf' , SIZE = 1000KB , FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

go

ALTER DATABASE [VCDB] SET RECOVERY SIMPLE

go

use VCDB

go

sp_addlogin @loginame=[vpxuser], @passwd=N'Password@1', @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

========================================================


※データベース作成に失敗した場合
SQL Server 2014 Management Studioを起動し、以下を削除後に再度実行する。
 [データベース] - [VCDB]の削除
 [セキュリティ] - [ログイン] - [vpxuser]の削除
 [データベース] - [システムデータベース] - [msdb] - [セキュリティ] - [ユーザー] - [vpxuser]の削除


------------------------------------------------------------
◆ ODBCの設定
------------------------------------------------------------

[サーバーマネージャー] - [ツール] - [ODBCデータソース(64ビット)] - [システムDSN] - [追加]
名前: SQL Server Native Client 11.0
[完了]

名前: vCenter Server
サーバー: (サーバー名)

● ユーザーが入力するSQL Server用のログインIDとパスワードを使う
ログインID:vpxuser
パスワード: *********

■ 既定のデータベースを以下に変更する
VCDB

[完了]
[データソースのテスト]
問題がないことを確認し、[OK]



Secret


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。