Set Database User Security

Owned by Former user (Deleted)
Last updated: Aug 21, 2012
3 min read


Security Approach
To operate the Workbench software, network users must have database security rights to:

  • the Workbench database(s)
  • the Workbench Licence database

 


To achieve this, a SQL User Group is created called Workbench_Users, being the user group that was setup in the Pre-Requisites and Assumptions section

Granting Access rights to SQL Server

  • In SQL Studio Manager click on the security option

  • Select Logins

  • In the area displayed above right mouse click and select "New Login"
  • Click the search button
  • Type workbench_users in the box provided




Make sure that Groups is selected in the Object Types and the Location is your domain.


You can change Object Type by clicking the Object Types button:


or Location by clicking the Locations button


You should now have a screen that looks similar to the one below

  • Click OK


Grant Database Access
This section selects the databases over which the User Group Workbench_Users will have rights to access data

  • Select the User Mapping page

  • Select the Workbench databases that you need the users to have access to.
  • Typically this would be WorkbenchLive, WorkbenchTest, and WorkbenchLicence
  • Click OK


Database Rights (wbGrantPermissions)
For users to use these databases, they need to be granted rights over them.
Rights to the selected databases are granted via a stored procedure called wbGrantPermissions.sql

 

  • In SQL Server click the new Query Button
  • In the Space provided type wbgrantpermissions '<Domain>\Workbench_Users'
  • Change the selected database to one of the Workbench databases you wish to grant rights to
  • Click Execute 

 

 

When the process is complete, all the messages displayed will be in black. Any red messages are errors which need to be resolved. You may need to contact Workbench International Limited for assistance in resolving these.

  • This process must be repeated for all the Workbench databases you need to grant rights to, including the WorkbenchLicence database
  • The running of wbGrantPermissions is fundamental to the operation of Workbench.
  • Any new or updated stored procedures supplied by Workbench as part of an upgrade are run against the database as part of the upgrade process. The running of these stored procedures will automatically run the wbGrantPermissions.sql stored procedure as well, granting the required permissions.


If there is doubt at any time over rights to the database then run the command wbgrantpermissions from the SQL Query option in SQL Server.

  • This will grant permissions on the selected database to users in the Group or Groups defined in the Control Parameter in Workbench called 'SQL User Logins', as displayed below.
  • When you have started Workbench, ensure that this Control Parameter is populated with 'Workbench_Users'