What It Is
Whenever MEX has to run user entered SQL queries (typically quick searches or custom fields), those queries are run against the database as the Readonly user. This is a separate database user that only has read access to the database, preventing end users from being able to run INSERT/UPDATE/DELETE/etc statements.
This user will always be called <databaseName>_Readonly. On a standard install this would be MEXDB_Readonly
Issues It Commonly Causes
A fairly common issue, especially after a migration, is that MEX runs into trouble with the readonly user, here are the most common issues caused by a broken readonly user.
- Custom Fields don’t load
- Quick searches don’t load (logged on users is a good one to test)
- Pages in MEX take just over 10 seconds to load. (the timeout for the queries sent to the readonly user is 10 seconds.)
How to Fix It
The most common issue after a migration is that the password on the logon for the readonly user is different to what MEX is expecting. This can be resolved by taking the output of
And entering it as the password for the readonly user. Depending on how the database security was setup, you may also need to uncheck the Login is locked out button on the Status page of the logon.
Other less common issues:
- Make sure the logon maps to the user with the same name on the database with the db_datareader role.
- Make sure the database user has the db_datareader owned schema.
- Check that the database user has execute permission on the following functions in the Securables tab. GetAssetCriticalityString, GetListingDescriptionShowLength, GetRequisitionRegionIDs. These permissions can be granted with the following script, replacing MEXDB_Readonly with the correct user.
The user MEX connects via the connection string has insufficient permissions to create users on the SQL server. This means the above fix will not work. Either setup permissions for the primary MEX user correctly, create a new user, or manually create a readonly user with the correct permissions and update the password in the SystemOption table.
If all else fails, delete the readonly user on both the server and database, then recycle the app pool.