Documentation Center

Optionally minimize the database user's roles and permissions

Describes how to optionally reduce the roles and permissions of the database user for everyday usage

About this task

Optionally execution to minimize the permissions for normal operations outside of the upgrade time frame.

Procedure

  1. On the database server, open the SQL Server Management Studio.
  2. In the left pane under the server name open Security > Logins.
  3. Right-click on the database user (e.g isource) and click on Properties
  4. Revoke server role sysadmin

    The server role sysadmin allows to perform any activity on the server. Content Manager DBUpgradeTool (DBUT) requires this server role for instance to create the standard database job and add extra (error) messages. So, in order to allow DBUT to fully execute all necessary tasks during an upgrade, we advice to grant the server role sysadmin again before every upgrade. However, during everyday usage you can revoke sysadmin using the following steps:

    1. Click Server Roles in the left pane.
    2. Deselect sysadmin in the right pane. Make sure that public is still selected.
  5. Revoke database role db_owner
    1. Click User Mapping in the left pane.
    2. Select the correct database in the upper right pane.
    3. In the bottom pane, uncheck the box next to db_owner . Make sure that public is still selected.
  6. Click OK.
  7. After revoking the database role db_owner, add the minimal required permissions

    In order for the Content Manager application to work without issues, the database user need the following permissions

    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • CREATE TABLE
    • CREATE VIEW
    • CREATE FUNCTION
    • CREATE PROCEDURE
    • REFERENCES
    • ALTER ANY SCHEMA
    • EXECUTE

    You can grant the necessary permissions to the database user using the following steps:

    1. In SQL Server Management Studio open the file corresponding with your SQL Server version
      • C:\InfoShare\App<projectsuffix>\TriDK\Database\SQLServer2012\Create\GrantPermissionsToDBUser.sql
    2. If your database user is not "isource", change "isource" to the correct database user
    3. Run the script as an administrator
    4. Connect with the database user (e.g. isource) and run the following query to check that the database user has the required permissions
      SELECT * FROM fn_my_permissions(null, 'DATABASE')