User roles for MSSQL Policy and Session Store with Policy Server

Products

• SITEMINDER

Issue/Introduction

 

When installing a Policy Server, which MSSQL roles needed to be given
to the user that connects to the database ?

  1) bulkadmin
  2) dbcreator
  3) diskadmin
  4) process admin
  5) public
  6) security admin
  7) server admin
  8) setup admin
  9) sysadmin

 

Resolution

 

The roles described are Server-Level Roles, and indeed, they might
be too permissive in light of your Business requirement (1).

When reading Microsoft documentation, it seems that some Type of User
and Roles can be defined to get a combination that will reflect the enterprise
Business needs (2)(3).

To find the correct way to restrict the user to connect to the
database with right to create, read, modify, and delete objects in the
database, please get in touch with the Database Microsoft Support to
understand the priviledges needed according to your internal security
constraints.

 

Additional Information

 

(1)

    Server-Level Roles
    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15

(2)

    Selecting the Type of User

      If the person or group that needs to access the database does not have
      a login and if they only need access to one or few databases, create a
      Windows user or a SQL user with password.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15

(3)

    Join a Role

      Assign permissions to roles, and then add and remove users and
      logins to the roles. By using roles, permissions do not have to be
      individually maintained for each user.

      SQL Server supports four types of roles.

      Fixed server roles
      User-defined server roles
      Fixed database roles
      User-defined database roles

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/join-a-role?view=sql-server-ver15