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
- On the database server, open SQL Server Management Studio.
- In the left pane under the server name, open Security > Logins.
- Right-click the database user (e.g
isource) and select Properties. - 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:
- Select Server Roles in the left pane.
- Deselect sysadmin in the right pane. Make sure that public is still selected.
- Revoke database role db_owner.
Note: Revoking the database role db_owner should be done only after revoking the server role sysadmin.
- Select User Mapping in the left pane.
- Select the correct database in the upper right pane.
- In the bottom pane, un-check the box next to db_owner. Make sure that public is still selected.
- Select OK.
- 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:
Name Description Usage SELECT Retrieve information/records from the database Everywhere INSERT Insert new records into the database Everywhere UPDATE Update records in the database Everywhere DELETE Delete records from the database Everywhere EXECUTE Execute a stored procedure in the database Stored 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 TABLE Create a new table DBUpgradeTool: 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 VIEW Create a new view DBUpgradeTool: during the upgrade of the database from one version to another version, we are dropping and recreating views. CREATE FUNCTION Create a new function DBUpgradeTool: during the upgrade of the database from one version to another version, we are dropping and recreating functions. CREATE PROCEDURE Create a new stored procedure DBUpgradeTool: During the upgrade of the database from one version to another version, we are dropping and recreating stored procedures. REFERENCES Create foreign keys between two tables DBUpgradeTool: 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 SCHEMA Create, alter and delete objects in any schema DBUpgradeTool: 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
dbofor the full-text search.You can grant the necessary permissions to the database user using the following steps:
- 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
- If your database user is not
isource, changeisourceto the correct database user. - Run the script as an administrator.
- 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')