Sage 500 ERP Security: SQL Server Roles

In versions greater than 6.0, Sage 500 ERP security is assigned by roles. When not employing the application role, a user logon is assigned to the ApplicationDBRole, which enables all permissions to all objects in the Sage 500 ERP application and system database (application database beginning with version 7.0). This allows access to the Sage 500 ERP data from any application that can be used to log on to SQL Server.

When using the application role, Sage 500 ERP logons can be assigned to the role. The role revokes permissions to all objects in the Sage 500 application and system database outside the Sage 500 application, unless the Allow Read Access option is selected for the user logon. This option grants the user logon SELECT permissions only on the tables so that the logon can be used to query data for use in FRx, Crystal Reports, and so forth.

Perform the following to configure the most secure environment within Sage 500 ERP:

  1. Create user logons within the Windows domain.
  2. Add all computers to the domain.
  3. Create security groups in SM Maintain Security Groups that are specific to the rights you with to enable for users in specific tasks. Set Excluded permissions for Desktop Customization to disallow users to modify their task menu.
  4. Configure Desktop menus specific to the tasks your users will use.
  5. Add the domain users in SM Maintain Users. Do not specify a Default Security Group, but add the proper security groups for each user for each company. Remember, task permissions work on an inclusion basis, so the user inherits the most permissive rights allowed within the groups that user belongs to within the company specified (see Help in Maintain Users for additional information).
  6. Select the ‘Use Application Role’ option within SM Maintain Users for all user logons.
  7. Select the Task Menu the user will use within Sage 500 ERP.
  8. Remove all SQL users from access to Sage 500 using SM Maintain Users.
  9. Select the ‘Use Windows Authentication’ option for all users in the Desktop Configuration Utility.

To learn more about Sage 500 ERP security and SQL Server Roles, download our free Sage 500 ERP Owner’s Manual available here, or by clicking the button below.

Sage 500 ERP security

Related Posts