Create a read-only SQL Server 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.

The following instructions are based on Microsoft SQL Server 2005 Express, but you’ll find the procedures to be very similar for other versions and/or editions.

  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.
    • Please refer to this article if you don’t see Management Studio on your server
  3. Select (local)\ACCTIVATE as the Server Name on the Connect to Server window, make sure Windows Authentication is selected and click Connect
  4. Expand the Securityfolder in the panel on the left
  5. Right-click on the Logins folder and select New Login…
    1. Enter the desired Login name(i.e. Website)
    2. Select the SQL Server authenticationoption
    3. Enter and Confirm the desired password.
    4. Un-check the User must change password at next loginoption
      • NOTE: You may also want to un-check the Enforce password policy option
    5. Select the Default databasethat corresponds to your Acctivate company
    6. Click User Mapping in the panel on the left
    7. Check the Map box adjacent to the database for each Acctivate company to grant access
      • NOTE: You can check the db_datareader option to provide full read-only access to the entire Acctivate database. Otherwise, use the procedures below to grant access to specific data views.
    8. Click OK to save and close

Use the following procedure to provide access to specific Acctivate data views:

  1. Follow steps 1-3 above to open SQL Server Management Studio
  2. Expand the Databasesfolder in the panel on the left
  3. Expand the database that corresponds to the Acctivate company (e.g., ACCTivate$Demo)
  4. Expand the Views folder
  5. Grant permission to each data view for the SQL Server user account:
    1. Right-click on the view and click Properties
    2. Click Permissionsin the list on the left
    3. Click Search…on the right and enter the new SQL Server login you created in step 5 above
    4. Click Check Names then OK
    5. Check the Grant box for the Select permission and click OK
  6. Repeat step 9 for each data view
Posted in Database, Installation - Last modified on September 9, 2015Cassandra Creasy
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.