When the database is created in Microsoft SQL database for the Workspace ONE Access service, the database user is granted to the db_owner role. Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

After the database is set up and configured in the Workspace ONE Access service, you can revoke access to db_owner and add db_datareader and db_datawriter as the database roles. Members of the db_datareader role can read all data from all user tables. Member of the db_datawriter role can add, delete, or change data in all user tables.

Note: If you have previously revoked access to db_owner role, make sure that the db_owner role is granted back before you start an upgrade to a new version of Workspace ONE Access or make any updates to External Database Settings such as changing database user passwords.

Prerequisites

User role for the Microsoft SQL Server Management Studio as sysadmin or as a user account with sysadmin privileges.

Procedure

  1. In the Microsoft SQL Server management Studio session as an admin with sysadmin privileges, connect to the database instance <saasdb> for Workspace ONE Access.
  2. Revoke the role db_owner on the database, enter the following command
    Authentication Mode Command
    Windows Authentication (domain\user) 
    ALTER ROLE db_owner DROP MEMBER <domain\username>;
    SQL Server Authentication (local user) 
    ALTER ROLE db_owner DROP MEMBER <loginusername>;
  3. Add db_datawriter and db_datareader role membership to the database.
    Authentication Mode Command
    Windows Authentication (domain\user) 
    ALTER ROLE db_datawriter ADD MEMBER <domain\username>;
GO

ALTER ROLE db_datareader ADD MEMBER <domain\username>;
GO
    SQL Server Authentication (local user) 
    ALTER ROLE db_datawriter ADD MEMBER <loginusername>;
GO
ALTER ROLE db_datareader ADD MEMBER <loginusername>;
GO