Sage 200: Creating a Roveel read-only SQL server user account

Roveel connects to the Sage 200 SQL Server database using a single SQL Server user account with read-only access. If other applications require restricted data access, you may need to create additional SQL Server user accounts. To do this, you’ll need to install Microsoft SQL Server Management Studio, which can be downloaded here.

The steps below should be carried out by your IT department or under the guidance of an IT professional. The Roveel support team cannot advise on SQL Server security.

Creating a User Login

1. Log in to the main computer (server) where Roveel is installed using a Windows account with administrator privileges.

2. Open Microsoft SQL Server Management Studio from the Windows Start menu under the Microsoft SQL Server folder.

3. In the Connect to Server window, select your server as the Server Name. This is typically formatted as SERVERNAME\S200, although it may differ if you’re not using the default Sage 200 instance. Consult your IT team for confirmation.

4. Sign in using either Windows Authentication (if you are a domain admin) or SQL Authentication using the SA account. Consult your IT team for the password or contact Roveel support.

5. Expand the Security folder in the left-hand panel.

6. Right-click Logins and select New Login…

7. Enter the desired Login name (e.g., “External Application”).

8. Select SQL Server authentication.

9. Enter and confirm the desired password. You may choose to disable Enforce password policy and Enforce password expiration. If you leave password policy enabled, you will have to update the details within the Roveel connector when the password updates.

10. Uncheck User must change password at next login.

11. Choose the Default database associated with your Roveel company.

12. Click User Mapping on the left.

13. Check the Map box next to the database for each Roveel company you want to grant access to.

    • Ensure Sage200Configuration database is checked to grant read-only access.

    • To provide full read-only access, enable db_datareader under Database role membership.

    • If you only want to grant access to specific views, click OK and proceed to the next section.

14. Click OK to create the login.

The account is now ready to be used by the requesting user or service.