Windows groups and sql logins

Question

Windows groups and sql logins

grashed

Grasshopper

Points: 10
More actions
November 27, 2008 at 8:44 pm

#132525

If a windows user is part of two distinct windows groups and each windows groups is mapped to a sql login on SQL 2005, and each of those sql logins has a different user for a specific database, how does sql determine which group to use for the user's login and consequent database user. Here is my example
Windows User: BOB
Bob is part of two windows groups, WinDoAlot,WinDoLittle.
Both WinDoAlot and WinDoLittle have sql logins on a SqlServer
Both sql logins have users on a particular Database, DBImportantStuff
Two users on DBImportantStuff are mapped to WinDoAlot and WinDoLittle. WinDoAlot's user (SuperUser) has owner access and the user mapped to WinDoLittle (WimpyUser)only has read access.
When BOB goes to logon to Sql, which group does sql use to login him in with (WinDoAlot or WinDoLittle)? How can I tell which group sql used?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Now there's a question that I have always wondered about...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I don't think you can tell which group is used and I don't think SQL Server cares. SQL Server just checks does this user have rights to the object(s) it is accessing.

I do know that SQL Server acts on least privileges. So if WinDoLittle has any explicit DENY's they will override any GRANT's for WinDoAlot (except for column level Grant's, an inconsistency that will be removed in a later version of SQL Server).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

forsqlserver SSCoach Points: 18902 More actions · Answer 3

Dear grashed

Is it possible,that two users with same name belongs to diff. wind's gr's can access one database.I don't think..

if yes please let me know...

Thanx

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 4

Dear

when you will login to SS then if you use windows authentication then the same windows gr will be responsible for your login...

or

if you use SQL server authentication then u can check the server properties by which login u have entered...

Thanx

Thanks