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:
Open SQL Server Management Studio
Connect to your SQL Server instance
In the Object Explorer, expand the server node
Right-click on Security → Logins
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:
In Object Explorer, expand Databases
Expand your target database
Right-click on Security → Users
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:
In the Database User dialog, click on Membership in the left panel
In the Database role membership section, check the box for db_datareader
Click OK to save the user
Testing the User Account
To verify the user was created correctly:
Open a new connection in SQL Server Management Studio
Use the new login credentials to connect
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 'Connectors' to see the list of connections.
Click 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 '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 [email protected]. If possible, we recommend that the IR is downloaded and the database user is created before the call.