We are migrating from SQL Server 2005 R2 to SQL Server 2019. I'm trying to find and fix any bad practices or head off any technical problems that might crop up from our old server. We set up SQL Server 2005 R2 many, many years ago and I know we haven't followed industry standards. I'd like to change that.
In my research for this project, I've read over and over that orphaned Users are a bad thing. Our system has some orphaned Users, but I can't see what might be bad about how we are doing it. Can anyone see a problem with the following setup? (Note: I'm adding prefixes to try to make the following more clear. We don't use the prefixes.)
1) We have an all encompassing Window's Authenticated Login called say L_AllStaffForAppA. L_AllStaffForAppA is mapped to an Active Directory group (ad_AllStaffForAppA). ad_AllStaffForAppA contains all staff who are allowed to use a database or application.
2) Each database then has a database User, u_AllStaffForAppA, which is mapped to the Login L_AllStaffForAppA. Basic permissions which apply to all users are is given to u_AllStaffForAppA. Thus, this setup gets everyone (who needs it) access to the relevant database/data.
3) The part that I don't know about: Not everyone is allowed to access/edit all the data in a database. So, in addition to the user listed in #2, databases also have additional Windows Authenticated database Users with these properties:
a) are mapped to Active Directory groups containing staff who are subsets of staff in ad_AllStaffForAppA.
b) are orphaned users (these sub-set users are not mapped to any Login directly themselves)
For example: A database may have a User called something like say u_SpecialLeaders. U_SpecialLeaders is mapped to an Active Directory group called ad_SpecialLeaders, which consists of staff who are also in ad_AllStaffForAppA. The database User u_SpecialLeaders is not mapped to a Login. u_SpecialLeaders is an orphan.
The security seems to work OK. For example, if the database User u_SpecialLeaders gets permissions to run a view, only staff in the Active Directory group ad_SpecialLeaders can run that view.
The question is: This system is straight forward and easy to manage. So, does it matter if u_SpecialLeaders is an orphan? Are there any significant drawbacks I just can't see?