NB: To complete this will require SQL server access for your internal database. Your internal IT team will need to be involved and if needed, be added as a FleetOps user with access to the Connectors panel.

Once you have already installed the Microsoft IR service to manage your initial database connection, you can use the same service to manage any additional database connections.

When setting up database integrations or granting limited access to applications, creating users with read-only permissions is a crucial security practice. This article will guide you through creating a SQL Server user that has SELECT-only access to a specific database using SQL Server Management Studio (SSMS).

Prerequisites

Before starting, ensure you have:
  • SQL Server Management Studio installed
  • Administrative access to the SQL Server instance
  • Access to the target database
  • Appropriate permissions to create logins and users

Step-by-Step Process

Step 1: Create a SQL Server Login

First, you'll need to create a login at the server level:
  1. Open SQL Server Management Studio
  2. Connect to your SQL Server instance
  3. In the Object Explorer, expand the server node
  4. Right-click on Security → Logins
  5. Select New Login...

In the Login - New dialog:

  • Login name: Enter a descriptive username (e.g.,fleetops)
  • Authentication: Choose SQL Server authentication
  • Password: Create a strong password
  • Confirm password: Re-enter the password
  • Important: Uncheck "Enforce password expiration" to prevent authentication issues when the password expires
  • Default database: Select your target database from the dropdown

Step 2: Create a Database User

After creating the login, you need to create a corresponding user in the specific database:
  1. In Object Explorer, expand Databases
  2. Expand your target database
  3. Right-click on Security → Users
  4. Select New User...

In the Database User - New dialog:

  • User type: Select SQL user with login
  • User name: Enter the same name as your login (or a descriptive variation)
  • Login name: Select the login you created in Step 1
  • Default schema: Leave as dbo (default)

Step 3: Assign Read-Only Permissions

Now assign the appropriate role to grant SELECT permissions:
  1. In the Database User dialog, click on Membership in the left panel
  2. In the Database role membership section, check the box for db_datareader
  3. Click OK to save the user

Testing the User Account

To verify the user was created correctly:
  1. Open a new connection in SQL Server Management Studio
  2. Use the new login credentials to connect
  3. Try executing a SELECT statement on a table in your database

Enter Details to FleetOps

You must have access to this panel. 

  • Login to FleetOps, go to 'Settings' by clicking on the settings icon from the menu.

  • Click on 'Connectors' to see the list of connections.

  • Click on the name of the vendor you are connecting to. 

  • Proceed to fill out the panel by adding the database connection details including the user credentials.

  • Click on 'Save & Check Connection'.

  • Once we have successfully connected, we will begin syncing the data from your database.

Note: If required you can schedule a call with our engineering team to guide you through the install process by emailing support@fleetops.com. If possible, we recommend that the IR is downloaded and the database user is created before the call.