Server Access "Via Group Membership"
-
OK I would really appreciate it if someone could explain to me how you get a login that has "Via Group Membership" listed in the server access column of the login.
I have a couple of logins in a database that have this and no idea how they got there...
Sorry for the short message I don't really know what else to report off at the moment and will await to see if anyone has some questions.
Thanks in advance,
Leeland
-
Under the Logins tab, under security, you may have Windows groups listed. I.e., BUILTIN/Adminstrators is added by default as a login and given SysAdmin access. This means you don't need an explicit login for any local administrators on the host OS.
-
Lee,
If you see "via Group Membership" for a user in a database, they are members of a Windows group that has been granted access to the database. Usually, you won't see anything for an individual until they create an object in the database.
Greg
Greg
-
OK I think I understand...
So basically if you have a group of (DOMAIN) users placed in a Domain group i.e. (DOMAIN\SQL_USERS), and then you give access the Doman GROUP to a database with certain permissions.
However if someone in that group attempts to connect to the server/database, they won't be able to until you grant that user a specific login to the server. By granting them a login you are not specifically giving them any rights to any databases, that security is handled by the group that they are a part of.
If this is somewhat correct it seems to be extra work vs. just creating the login and doing the specific access for that person. I guess I can see the point of managing a large number of people's access if they all needed the exact same acess but if you had to make changes to individual people it would defeat the purpose.
-
You've almost got it, Lee. A member of a group with access to the server and database WILL be able to connect without having an individual login in SQL Server. You only need to create an individual login if the person needs different permissions than the whole group.
Groups can save you work if you have a bunch of people who need exactly the same permissions in SQL Server. You're right, though, that if each individual needs different database permissions, groups don't save you any administrative work.
Greg
Greg
-
So it acts similar to a role you could create and assign people to the groups then...to me that would be easier to manage and figure out what access is given...but that might just be me.
And I have one other question...so if you just have to give the group rights to a server and database, is that the only way you would know what access they have? Put another way, lets say someone says to me...I want the same access as person X. But person X doesn't have a specific login, they would only have a login via the group.
Unless I am mistaken, you would have to find out what groups the person is a part of, and then search through them to find out what group to mimic in regards to access. And if the person is in multiple groups for instance how could you even know what to choose for access? I mean that is an extreme example but the point I am making is it seems like more work when you could just make roles and put people in them...and be able to track what individual access each person has.
Does that make sense what I just asked? it would seem to me if I am correct that it could get quite confusing.
-
From my dim recollection of managing user permissions under an NT domain, the principle was
- create a Local group on the system where a resource (eg SQL Server) exists eg L_DBNAME_READONLY
- grant this Local group the relevant permissions (eg corresponding to a role)
- create a Global domain group to match the local group eg G_DBNAME_READONLY
- add domain accounts to this Global group
- make this Global group a member of the Local group - this sounds counter-intuitive, but gives the global group local permissions
This means that access to a machine resource is always mediated through Local groups on that machine, and you can track back from the Local group to the Global group(s) it contains, to the individual domain accounts in that group.
I have no clue how Active Directory changed all this - we still run an NT domain, and this is how I manage access when not using SQL authentication.
-
"Unless I am mistaken, you would have to find out what groups the person is a part of, and then search through them to find out what group to mimic in regards to access. And if the person is in multiple groups for instance how could you even know what to choose for access? I mean that is an extreme example but the point I am making is it seems like more work when you could just make roles and put people in them...and be able to track what individual access each person has.
Does that make sense what I just asked? it would seem to me if I am correct that it could get quite confusing."
Well, I guess its whatever works for you.. in Active Directory you can easily see what groups a person is a member of.. you can put those groups into roles on the DB/server or just assign rights to those windows groups. Which makes things a lot easier if you have a large number of users and they are broken into various functional groups. Otherwise you are busy trying to track down individual users through your roles..
-
I can't explain it better than Ewan did. You just have to remember that the group is a Windows entity, not a SQL Server entity. You'd have to know your way around the Windows network and/or Active Directory. We have network admins who can tell us what groups someone belongs to. Once you know the names of the groups, you can see what access they have in SQL Server.
Check out the overview of groups in SQL Server BooksOnLine for some examples of granting access and permissions to groups and individuals.
Greg
Greg
-
good point, sounds like a case of 6 of one and half a dozen of the other
Personal preference I guess. -
I will take a look at BOL for groups to get some more information...Thanks Guys.
Lee
-
I hope we're not discussing a production system which stores sensitive/valuable data ?? Please tell me you don't work for a bank or credit card company and preferably you don't work in the UK < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
This is a production box and I am cleaning up accounts after a migration off of a previous system prior to me working in my current position. I came across these accounts that had the SID's all messed up from the move (user database not in sync with the master). I was in the process of re-creating the logins ect when I came across a couple that had this "via group membership" in the server access column which I had seen previously but never really paid too much attetion to.
Well this is me paying attention and attempting to gain an understanding to the point of "via group membership" and if it is better/worse than setting up individual accounts and assigning roles (something I am more comfortable with and more experience) vs. not understanding the gravity of "via group membership" and how it functions in regards to security ect...
In this case it was a very small group, only 4 users so I just re-created the user login's and made a role for the access.
-
My answer to someone that says, "Give me the same rights as user X," would be, "No. Tell me what rights you need and I will give you the appropriate rights based on what you need and what you are authorized to have."
If they can't explain what they need, then they must not need it. At the very least, they should be able to explain what they need to do in the database.
Let's suppose you gave user Y the same rights as user X when what user Y really needed to be able to do was to view sales data (which he knows user X can do). Now, let's suppose user X is one of the few people allowed to enter and modify sales data. User Y accidentally deletes all of the sales data. Who's going to be in hot water? Him for accidentally deleting it, yes. But also you for giving him the ability to delete it when he shouldn't have it.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
You have a great point. I did not mean to imply that was the case, that users simply say I need this access..ect and it is magically given without question.
In my mind I was thinking of a situation where you did have a user that was either going to replace someone that left...or be someones backup. Where they would need to have the same or similar access.
The point was to illustrate crudely, that in my mind or at least in my simple understanding it could be difficult to determine access if the user you are trying to model has access "via group membership". The way I see it is if you go to look for that persons login you wouldn't find a specific login for them because they are granted access via the group membership so I am led to believe based on Greg Charles reply early in this post.
If that is the case you would have to go hunt down a network guy to find out what group or groups that person were in and then cross reference it against what is in the security tab on the server, then review the access for the new persons account. I would like to think that it could get even more confusing if the same person has multiple groups that they are part of which you would have to go through as well and see what security over laps...ect.
I would like to think that I would never be so nieve as to just give someone access like you gave as an example above..
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply