(new) tsf_user function to return name/email instead of OpenID sub

Does the function dbo.tsf_orginal_login() not resolve your problem?

That will return the original user in case of simulation, but that may also be a sub

This is quite an challenge, when you want to see (readable and understandable) who inserted/edited a record. I guess the usr_id will be varying in time for the same person. I find it necessary, though.

Indicium does have all the necessary information to create a session variable for it. We could thereafter also supply a function, let's say tsf_usr_name, that returns that value or else uses the tsf_original_login function to supply a value.

Would that be a suitable solution?

That would be perfect!

This should be merged with my post on the same topic.
( A little improvement of the tsf_user() function ).

FYI: ​@Anne Buit is looking into this, we might go for a Thinkstore solution here.

Another thing to note: ​@Arjan Sollie recently discovered that the sub is actually unique across different Applications in the Microsoft Entra world, this means that a User could have a different sub in Production versus Acceptance environments. Instead of using the sub, you might want to use the oid in order to have an identifier that is identical in both IAMs:
ID token claims reference - Microsoft identity platform | Microsoft Learn

We’d like to limit the number of session variables as much as possible. While they are small and lightweight, they have to be initialized for every single database call.

Volatile data such as the user id, user ip, session id, original login, effective application language (subject to fallback mechanisms) and the application id (there may be multiple per application database) are chosen as session variables as you cannot retrieve them from elsewhere.

Any data which can be derived from IAM, such as the user name, e-mail address, user group membership and such we’d rather load on-demand or synchronize between IAM and the application database.

Generally, an administration of users is always present within an application database. For instance, to link users to cases, projects, planning and such. A duplicate administration (add the user to IAM, add the user to the product database) is a hassle.

Our advice would be to set up a scheduled system flow which duplicates the necessary user data from IAM into your product database using an application connector.

​@Robbert van Tongeren agreed to convert this to an idea for the Thinkstore to offer a plug-and-play user synchronization mechanism for user id, name, email?