We are working within the bounds of the application (our primary accounting software which uses a SQL Server DB for data storage) and so our choices of authentication type are limited to SQL Security only. NT Authentication will not work with the apps own proprietary security. As is common the app developers created their own security scheme instead of using what was already provided in SQL Server.

Within the aps security mechanism each user does have a unique numeric ID that is never seen (at least not from within the app) and so changes there do not matter. Where the name change is an issue comes into play when we are trying to sync into the apps processes (via T-SQL) and capture information that the app should but does not such as data change auditing. By assigning users with logins that match their name (which is how the application admins set up users within the app) we are able to do things that even the aps maker had previously said was not realistic. Thanks to native SQL items like ORIGINAL_LOGIN we can link up process to users that the app does not naturally provide.

If we had our users setup per the software vendors specs then every user would be connecting in as the login that owns the DB. That means that 50 users connections all come in as the same ID and it becomes impossible to determine who is doing what unless the apps native security functionality provides a way to do this and it doesn't. So while the use of names to logins via standard SQL Security is not the best choice under ideal circumstances its what we have to work with for now.

Thanks for posting