auxalia aktuelles DE

Permissions for SQL Server Users When Using PlantDataManager

Written by auxalia Team | Jul 10, 2024, 7:54:20 AM

In this blog, we will explain the permissions required for SQL Server users when using PlantDataManager. This information is aimed at technical users and administrators responsible for managing and configuring SQL Server environments.

If the necessary permissions are not set correctly, users will receive an error message similar to the following when attempting to make changes in PlantDataManager:

Overview of Permissions

For the standard use of PlantDataManager (PDM) by standard users with the server role `public`, editing and importing data are possible without issues. However, the initial setup requires special permissions.

Initial Setup

Since version 7.3.0.3767 of PlantDataManager for SQL Server projects, individual databases are created for each user. This means that a database in the format `PDMDataCache_Username` is created for each user.

To create these databases, the user using PlantDataManager must have the server role `dbcreator`. This role allows the user to create new databases on the SQL Server.

Ongoing Operations

Once the database for the user is created, the `dbcreator` server role can be removed if desired for security reasons. The created database will then only be filled with content or emptied (e.g., after an import). For ongoing operations, only read and write permissions are required.

Technical Background

The approach of creating an individual database for each user increases data security and simplifies handling and consistency for PlantTools between collaboration projects and network projects. A reference file (PDMDataCache_Username.dcf) is created in the project folder, and a corresponding database is created on the SQL Server. This process requires that the user can create a database themselves.

Quick Guide: Setting Temporary Permissions via SSMS

Below is a step-by-step guide on how to temporarily set permissions via SQL Server Management Studio (SSMS) so that the user can make changes in PlantDataManager, and how to remove these permissions afterward.

Step 1: Connect to SQL Server

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to your SQL Server.

 Step 2: Set the `dbcreator` Server Role

  1. In the Object Explorer, navigate to **Security > Logins**.
  2. Select the relevant user, right-click on it, and choose **Properties**.
  3. Go to **Server Roles**.
  4. Check the `dbcreator` role and click **OK**.

 Step 3: Perform User Changes in PlantDataManager

  1. Allow the user to make and save changes in PlantDataManager. These changes can then be deleted again. This will create the `PDMDataCache_Username` database.

 Step 4: Remove the `dbcreator` Server Role

  1. Navigate again to **Security > Logins**.
  2. Select the user, right-click on it, and choose **Properties**.
  3. Go to **Server Roles**.
  4. Uncheck the `dbcreator` role and click **OK**.

Conclusion

Setting up the correct permissions for SQL Server users when using PlantDataManager is crucial for smooth operation and data security in your projects. By following the steps outlined above, you ensure that the necessary databases can be created without permanently granting unnecessary permissions.
View full post