[print_link]
Working at a customer with three Virtual Centers (VC), I needed a way to document the security roles and their permissions, and a easy/consistent way to apply these role/permissions to the other VC’s.

So I created an Excel sheet to generate SQL files per role (incl. permissions), which can be imported directly in the VC (MS-SQL) Database to create the roles.

Scroll down to the end of this post to download the sheet.

Note: This sheet is created and tested on VC 2.0.1.

How to use the excel sheet and the generated sql files.

  • Open the sheet in excel. When you open the excel sheet allow macro’s to execute.

excel-1.jpg

  • Fill in the names of the roles in the first cell of the column, eg. replace the text ‘Role 1′ with ‘Operations’.

excel-3.jpg

  • If the permissions in columns are set, press ‘Generate SQL Role.

excel-4.jpg

  • Select the role to generate and press ‘OK’.

excel-5.jpg

  • The <role_name>.sql file will be written in the c:tempvc-sql folder.
  • Logon to the MS-SQL server with Administrator rights to the Virtual Center Database.
  • Copy the sql file on to the MS-SQL server, eg. ‘c:temp’
  • Start the ‘Microsoft SQL ServerQuery Analyzer’.
  • Log on to the SQL server (local) with windows authentication.

connect-sql.JPG

  • Choose in the tool bas the right VC database.
  • Open the <role_name>.sql file with File -> Open.
  • The sql statements of the <role_name>.sql file is displayed in the Query screen.

query-1.jpg

  • To execute the sql statements on the selected VC database, press the green arrow on the toolbar. arrow.jpg
  • The result of is displayed below the Query screen.

query-2.jpg

  • To see the role in the virtual center, restart the virtual center service on the VC server.

vc-11.jpg

  • In the column you will see the new ‘Operations’ role added.

vc-2.jpg

  • When the ‘Operations’ role is checked you will see the permissions.

Download

  VI roles and permissions Excel sheet v0.1b (94.5 KiB, 880 hits)

Update 14-01-08

Find here a schema of the VC database.