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.
- Fill in the names of the roles in the first cell of the column, eg. replace the text ‘Role 1’ with ‘Operations’.
- If the permissions in columns are set, press ‘Generate SQL Role.
- Select the role to generate and press ‘OK’.
- 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.
- 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.
- To execute the sql statements on the selected VC database, press the green arrow on the toolbar.
- The result of is displayed below the Query screen.
- To see the role in the virtual center, restart the virtual center service on the VC server.
- In the column you will see the new ‘Operations’ role added.
- When the ‘Operations’ role is checked you will see the permissions.
Find here a schema of the VC database.