Creating a read-only SQL Server user Account.

Steps to create a read only SQL user account for the Acctivate database

Acctivate connects to the Microsoft SQL Server database using a single SQL Server user account with full access. You may need to configure additional SQL Server user accounts for other applications to restrict data access. You'll need to install Microsoft SQL Management Studio to do so. You can find the install here.

The following steps should be taken by your IT department or at the advice of an IT professional. The Acctivate support team is unable to provide advice on SQL server security.

Creating the User Login:

1. Log in to the primary computer (i.e. server) where Acctivate is installed with a Windows user account with administrator privileges.
2. Open Microsoft SQL Server Management Studio from Windows Start menu, in the Microsoft SQL Server folder.
3. Select your server as the Server Name on the Connect to Server window. This will typically be something like SERVERNAME\Acctivate although could be different if you're not using the default Acctivate instance name. Please consult your IT for more info.
4. Login either using Window Authentication if you're a domain admin, or with SQL authentication using the SA user account. Please consult your IT for the password, or reach out to the Acctivate support team.
5. Expand the Security folder in the panel on the left.
6. Right-click on the Logins folder and select New Login…
7. Enter the desired Login name (i.e. External Application)
8. Select the SQL Server authentication option.
9. Enter and confirm the desired password.
   1. Optionally you may want to uncheck the Enforce Password policy and Enforce Password expiration options. If you opt to Enforce password policy, you can see the guidelines here.
10. Un-check the User must change password at next login option.
11. Select the Default database that corresponds to your Acctivate company.
    1. At this point your login should look similar to the following:
       1. 
12. Click User Mapping in the panel on the left.
13. Check the Map box adjacent to the database for each Acctivate company to grant access.
14. If you want to give read-only access to the entire database, then check the db_datareader checkbox on the bottom section under Database role membership
    
    1. If you only want to grant access to specific views, click the OK button to exit the window and continue to the next portion of the guide.
15. Click OK.

At this point you're done and ready to give the user account to the user/service requesting it.

(Optional) Assigning specific views to the SQL user account:

There may be times where you do not want to grant full access to the database and may only want to provide access to specific views such as orders, or customers. These steps will show you how to do so. You can refer to our doc's page for information about the various views to help determine what views to give access too.

1. Expand the Databases folder in the panel on the left.
2. Expand the database that corresponds to the Acctivate company (e.g., ACCTivate$Demo)
3. Expand the Views folder.
4. Grant access to the view with the following steps:
   1. Right-click one the view name and select Properties.
   2. Click Permissions in the list on the left.
   3. Click Search... on the right
   4. Enter in the SQL Login name you created in the section above in the box.
   5. Click Check Names and then click OK.
      1. If you get a warning that the name doesn't exist, double check you entered it in properly.
   6. Select the checkbox in the Grant column for the Select row.
      1. 
5. Click OK to exit the window.
6. Repeat step 4 for each view you'd like to give access too.