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

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

Procedure

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

    The server role sysadmin provides the ability to perform any activity on the server. Content Manager DBUpgradeTool (DBUT) requires this server role to create the standard database job and add extra (error) messages, for example. In order to allow DBUT to fully execute all necessary tasks during an upgrade, we advice to reinstate the sysadmin server role for the duration of that task. However, during everyday usage you can revoke sysadmin using the following steps:

    1. Select 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. Select User Mapping in the left pane.
    2. Select the correct database in the upper right pane.
    3. In the bottom pane, un-check the box next to db_owner. Make sure that public is still selected.
  6. Select 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 needs the following permissions:

    NameDescriptionUsage
    SELECTRetrieve information/records from the databaseEverywhere
    INSERTInsert new records into the databaseEverywhere
    UPDATEUpdate records in the databaseEverywhere
    DELETEDelete records from the databaseEverywhere
    EXECUTEExecute a stored procedure in the databaseStored procedures are used everywhere to:
    • Create new objects (maps, topics, publications, users, LOV values...) and get the newly created IDentity back.
    • Delete objects
    • Cleanup left-overs during a nightly maintenance job
    • ...
    CREATE TABLECreate a new tableDBUpgradeTool: during the upgrade of the database from one version to another version, we might introduce new tables.

    Full-text search: out-of-the-box the tables for the full-text search are created when you start the Crawler for that installation for the first time.

    CREATE VIEWCreate a new viewDBUpgradeTool: during the upgrade of the database from one version to another version, we are dropping and recreating views.
    CREATE FUNCTIONCreate a new functionDBUpgradeTool: during the upgrade of the database from one version to another version, we are dropping and recreating functions.
    CREATE PROCEDURECreate a new stored procedureDBUpgradeTool: During the upgrade of the database from one version to another version, we are dropping and recreating stored procedures.
    REFERENCESCreate foreign keys between two tablesDBUpgradeTool: During the upgrade of the database from one version to another version, we might introduce new tables and add new foreign keys between those new tables and the existing tables.

    Full-text search: out-of-the-box the tables for the full-text search are created when you start the Crawler for that installation for the first time, and we are also adding new foreign keys between those new tables and the existing tables.

    ALTER ANY SCHEMACreate, alter and delete objects in any schemaDBUpgradeTool: during the upgrade of the database from one version to another version, we are creating/altering/deleting tables, views, functions, stored procedures... in the schema dbo.

    Full-text search: we need to create the tables in the schema dbo for the full-text search.

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

    1. In SQL Server Management Studio open the file corresponding to your SQL Server version:
      • for SQL server 2016: C:\InfoShare\App<projectsuffix>\Database\Common\SQLServer2016\Tools\GrantPermissionsToDBUser.sql
      • for SQL server 2017: C:\InfoShare\App<projectsuffix>\Database\Common\SQLServer2017\Tools\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')