Creating a View Only SQL User in FlexNet Manager Suite

FlexNet Manager Suite 2020 R1 (On-Premises)

This task outlines how to create and configure accounts for the SQL user and to promote this user to a FlexNet Manager Suite View only operator. These accounts are required to fetch data from FlexNet Manager Suite using BMC Atrium Integrator Job and to populate the BMC Atrium CMDB Intermediate database.

To prepare the view-only SQL user for FlexNet Manager Suite compliance database, begin by creating a user (for example, fnmsintegrator) for the FNMSCompliance database that can log in using SQL Authentication as follows:

1. Open the Microsoft SQL Server Management Studio.
2. Expand the Security folder (first-level child of the top SQL Server node) and right-click Logins.
3. Right-click fnmsintegrator user and select Properties from the context menu.
4. Configure the Login properties for fnmsintegrator user as follows:
   1. Set the Server Role as public to grant server-wide security privileges to the user.
   2. In the User Mapping section, select FNMSCompliance database for this user.
   3. Select the public and db_datareader role membership for this user against FNMSCompliance database.
   4. Select db_datawriter membership only if the fnmsintegrator user is used for populating data to FNMSCompliance database from IntermediateDB.
5. Add the above user to the ComplianceOperator group for the FNMSCompliance database. For example, the following SQL query can be used for adding a fnmsintegrator user:
   Insert into ComplianceOperator(OperatorLogin,IsEnabled,GlobalOperator,Interactive) values('fnmsintegrator',1,0,0)
6. In FlexNet Manager Suite, navigate to the system menu ( in the top right corner) and choose Accounts.
7. Grant View Only permissions to the operator that you created in the above steps.
8. Connect to SQL Management Studio as the above user to validate that these read permissions have propagated and run the following query.

   select * from ComplianceComputerCurrentUser

9. If the query does not return results, execute the SyncPreCalculatedAccessRightsForOperator stored procedure for this ComplianceOperator as follows:
   1. Open Microsoft SQL Server Management Studio.
   2. Login to FNMSCompliance database as Administrator.
   3. Execute the following against corresponding Tenant UID.

      SetTenantUID '[Tenant UID]'
                                  Declare @OutputBit BIT = 1
                                  Declare @OperatorID INT = (select ComplianceOperatorID from ComplianceOperator where OperatorLogin = 'fnmsintegrator ')
                                  Exec SyncPreCalculatedAccessRightsForOperator @OperatorID, 0, @OutputBit
                              

      For example, the last line may be:

      EXEC SyncPreCalculatedAccessRightsForOperator 2,0,1

10. Retry the SELECT on ComplianceComputerCurrentUser to ensure it works correctly.
11. Ensure that the exporter utility config file is using this SQL user in the connection string for the FNMSCompliance database.