When possible, authenticate Microsoft SQL Server using the Windows integrated authentication utility.
Creating a SQL Server logon that maps to a local Windows group or an Active Directory group allows any user assigned to that group to log on to SQL Server. Using groups results in easier configuration and oversight.
Microsoft SQL Server manages authentication on two levels:
- Logins are granted access to the server. These logins can be associated with one or more users, and each user is granted access to a single database. To aid in maintenance, actual privileges are assigned to logins and users by roles. Microsoft SQL Server distinguishes between server and database roles.
- Hyland RPA products have some basic requirements in the database and its
configuration. At the login level these requirements are:
- The default language must be set to us_english.
- The login must be a member of the server role public.
For information on how to create a login in Microsoft SQL Server Management Studio, see refer to Create a Login in the SQL Server section on the learn.microsoft.com website.
For information on how to create a login in any Microsoft SQL Server client, refer to CREATE LOGIN in the SQL Server section on the learn.microsoft.com website.
User privileges are configured using database roles. By default, Hyland RPA requires each user to be a member of the db_owner role. The db_owner role is the only role that grants execute rights on stored procedures. Therefore, every user of the Hyland database must meet the following requirements:
-
Member of the database role db_owner
-
Not a member of any deny* database role.
Since the db_owner role provides all privileges for the corresponding database, including schema changes, a new role should be created to allow the execution of stored procedures.
To grant this role, run the following script while connected to the Hyland database:
CREATE ROLE db_executor GRANT EXECUTE TO db_executor
You can apply more restrictive requirements to users once this custom role is created:
-
Member of the database role db_datareader
-
Member of the database role db_datawriter
-
Member of the database role db_executor
-
Not member of any deny* database role
To update the database schema, you must still have a user who is a member of the db_owner role. All other users can run with limited privileges.
For information on how to create a user and assign it to a logon in Microsoft SQL Server Management Studio, refer to Create a database user in the SQL Server section on the learn.microsoft.com website.
For information on how to create a user and assign it to a logon in any Microsoft SQL Server client, refer to CREATE USER in the SQL Server section on the learn.microsoft.com website.
For information on how to assign logins or users to roles, refer to Join a Role in the SQL Server section on the learn.microsoft.com website.