When the saas schema is used to create the Microsoft SQL database for the VMware Identity Manager service, the database role membership 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 VMware Identity Manager 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 revoke access to db_owner, make sure that the db_owner role is granted back before you start an upgrade to a new version of VMware Identity Manager.

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 VMware Identity Manager.
  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