cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Unable to receive results from a few SQL Tables in FNMSCompliance Database

We are trying to give an employee db_datareader rights to the FNMSCompliance database.  He can run some queries and receive results but other queries return 0 records.  The same query returns data for another user account.

We made this user an admin in FNMS Accounts but he still gets 0 results.

Our DBA did some investigative work on the exact tables that are not returning records.

SELECT COUNT(*) FROM dbo.EntitlementProcessInfoDeferred

SELECT COUNT(*) FROM dbo.EntitlementProcessInfoProcessed

SELECT COUNT(*) FROM dbo.LicenseComplianceSummary

SELECT COUNT(*) FROM dbo.PublisherComplianceSummary

He said it seems to be related to this:  LicenseComplianceSummaryAllowed

Does anyone have any ideas?

Thanks!

(1) Solution
mfranz
By Level 17 Champion
Level 17 Champion

Hi Joan,

Is the account used the access the DB the same that has been made Admin in FNMS? And did he actually use that account on the FNMS WebUI yet?

I have heard from a colleague that the actual rights are only written when the account is being used on the WebUI. They rights data also seems to  be "forgotten" when an account does not login for a while. Not completely sure how ist done and what the threshold are, but it sounds like it's done to improve performance (by reducing the amount of cached rights data).

Best regards,

Markward

View solution in original post

(3) Replies
mfranz
By Level 17 Champion
Level 17 Champion

Hi Joan,

Is the account used the access the DB the same that has been made Admin in FNMS? And did he actually use that account on the FNMS WebUI yet?

I have heard from a colleague that the actual rights are only written when the account is being used on the WebUI. They rights data also seems to  be "forgotten" when an account does not login for a while. Not completely sure how ist done and what the threshold are, but it sounds like it's done to improve performance (by reducing the amount of cached rights data).

Best regards,

Markward

Thank you Markward!  That was exactly the problem.  This user doesn't need to do anything in the WebUI so had not launched it.  Once he launched it, he was able to run the queries successfully.  (Yes he is using the same account for both FNMS and SQL access).

Appreciate that tip!  Our DBA spent quite a bit of time diving into the backend tables and we figured out we needed to add the user's account to FNMS but  it never occurred to us to have him launch the WebUI.

Thanks!

Joan

 

Hi Joan,

That sounds actually like a topic that we stumbled upon the last days. We needed to have data extracted which is scoped by a respective operator.

To be honest, we did not deep dive into that topics completely, but that's what we know:

1. By first login of an operator, the user will be set to "isActive" internally. That is your possibility to check, if scoped content is available for that respective your. You can check the "isActive" flag in the ComplianceOperatorAudit table.

2. After a certain threshold, the operator will be set to "isActive = 0". That removes the permissions again. We guess that a value in the ComplianceTenantSetting table controls that behavior.

3. However, I wrote a script, which does actually the same steps when an operator logs in 😉 Of course, the user need to be set up with dedicated FNMS roles. You only need to define the operator ID in the attached script. The user will be set to active with that LastActive time and all permissions a synched from the roles for scoping purposes. 

 

Let me know, if that helps you.

 

Best regards

Martin