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?
July 1, 2020 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 2, 2020 at 8:41 pm
I guess my question would be, "Why do you want to keep them?". Leaving any orphaned objects, whether it be users, views, sprocs ... can only add confusion and possible issues. Let's say you leave the orphaned group and a year later, the network fellows decide to re-use the group for another purpose. The users in that group will now have access to your SQL server in whatever capacity that group had.
It's best practice to be rid of orphaned objects, especially users.
July 2, 2020 at 9:28 pm
Davis: Thanks for answering my question!
To answer your question about why I keep the orphaned users: The Users from group #3 above are required, because I have to have a way to give sets of staff special permissions to access or edit certain data. My two choices are to either leave the Users/setup as it is, or add a (unnecessary/duplicative?) Login for those Users.
My understanding is that a Login gets someone into the server. All my users access the server and main data in the database through the "L_AllStaffForAppA" Login. If staff need special permissions, they are already logged in. They get their special permissions by belonging to a special Active Directory group and thus a special User.
However, your point about best practices is exactly why I'm asking this question. I keep seeing written exactly what you are saying. I just can't figure out why/how it applies to my situation. Is there some reason I should add the Login for those particular orphan Users? What am I not understanding?
I'm afraid I don't understand your point about the network fellows. Say I have these groups: MyAppAllUsers and MyAppSupervisors. Our network people who manage the Active Directory groups would know by the group names that both of those groups are only for my application. What if they didn't know that? Even if I created a Login for MyAppSupervisors, would there be anything to stop the network people from putting inappropriate people into the group? Ie, how does giving a User called MyAppSuperivors a Login of it's own (making it no longer an orphan) change what network people might think to do regarding who they put into the Active Directory group for MyAppSupervisors?
I'm sure you are right. I'm just saying I don't understand your point or what could go wrong.
So, are you saying if they wanted one level of permissions, they use one login, but if they need another, they use a different login? If so, then that login information is shared amongst those who need that level of access?
As far as the network folks, what I'm talking about is, let's say you have an AD account, ad_usrWidget which has access to DB's X, Y & Z with read and write permissions. Time progresses and you need another group which has dbo level permissions, ad_usrDev. The users are cleared out of ad_usrWidget and users are added to ad_usrDev and ad_usrWidget collects dust. Now, another app needs access to the server which needs read access to DB's L & M. The network folks decide to use the ad_usrWidget account for that access. The problem is, they also now have r/w access to DB's X, Y & Z, which may get overlooked and leaves those DBs vulnerable.
Small risk perhaps, but why take them? I've been a DBA for over 20 years and whatever you think can't happen, can happen. Believe me. Mitigate all of your risks at your earliest convenience. Always take the time and effort to follow best practices. It pays off.
July 2, 2020 at 10:27 pm
I think I see your point! Thank you for taking the time to clarify. I will make sure all my Users on my new server have Logins. No more orphans for us!
FYI: It's not that I don't want to follow best practices. It's just that I have to understand why it is a best practice. Not all generic best practices apply to every situation in my experience. (Not as a DBA, but as a programmer.) In this case, I had not fully understood the implications of what I was doing.
Just to clarify myself: re: "So, are you saying if they wanted one level of permissions, they use one login, but if they need another, they use a different login?"
No, I was saying the opposite. What I have now (but I will change it) is a single Login and multiple Users. Some of the users are orphans, but the staff in those orphans are also in the main "AllUsers" User/Login/Group so they have access to the server. I used the Login as a way just to get staff onto the server. I used the Users in the databases to assign permissions to views/functions/etc. The problem was that the way I was thinking of it/understanding how it really works was wrong. Now that I thought it through with your reply, I see my error.
I'll fix it going forward. Thank you!
July 24, 2020 at 6:08 am
This was removed by the editor as SPAM
July 24, 2020 at 2:24 pm
Sorry, whitedotqe, I don't know your particular situation.
What I don't understand is why anyone would want to leave them orphaned? What is the use case?
If you have a legitimate business reason to keep orphaned users, then please do.
August 2, 2020 at 10:34 am
This was removed by the editor as SPAM
August 7, 2020 at 7:18 am
This was removed by the editor as SPAM
January 6, 2021 at 3:33 am
This was removed by the editor as SPAM
January 16, 2021 at 10:26 am
This was removed by the editor as SPAM
January 16, 2021 at 10:27 am
This was removed by the editor as SPAM
January 29, 2021 at 6:39 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply