SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacing individual logins with AD Groups potential pitfalls?


Replacing individual logins with AD Groups potential pitfalls?

Author
Message
hanrahan_tim
hanrahan_tim
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 126
Hello,

I have inherited a SQL server instance which host several databases used by in house .NET apps. All apps use windows authentication. For some reason, each user has their own login and user record. This is making support a night mare and I am looking to take advantage of AD groups to make this cleaner and easier to support.

My thought is to use an AD account like "authenticated users" and use this for logins so all company users get a login. I would then use specific AD groups I create to map to database roles for specific database related permissions. Is this a good practice? And if so, is "authenticated users" the right group to use to grant everyone a login to the server?

I was also wondering if I will need to remove each login and user before I add the AD group? It would seem if I don't, and then add the AD group any given user would then have two logins? I have also noticed that specific users have a default database set in their login, once switched over to using an AD group for logins how can this be accomplished?

Thanks in advance.
Andreas.Wolter
Andreas.Wolter
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1056
hanrahan_tim (10/9/2013)
Hello,
...
My thought is to use an AD account like "authenticated users" and use this for logins so all company users get a login. I would then use specific AD groups I create to map to database roles for specific database related permissions. Is this a good practice? And if so, is "authenticated users" the right group to use to grant everyone a login to the server?

I was also wondering if I will need to remove each login and user before I add the AD group? It would seem if I don't, and then add the AD group any given user would then have two logins? I have also noticed that specific users have a default database set in their login, once switched over to using an AD group for logins how can this be accomplished?

Thanks in advance.

Using Windows Groups instead of individual Logins is indeed a recommended practice
Authenticated Users would work, if you really want ALL AD Users being able to Access SQL Server. That one I wouldn’t consider a “good practice”, but if you really want to do that without exceptions, that’s the way. Otherwise you are better off creating an extra Win Group with ~90% of all Logins inside.
You do not have to remove the Logins before adding the group, but in the long I would advise doing so. Until then all those have 2 different access paths.
For ONE Group-Login you of course can only have ONE “default database” set.
Another reason to use multiple groups..

Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
hanrahan_tim
hanrahan_tim
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 126
Andreas.Wolter (10/10/2013)
hanrahan_tim (10/9/2013)
Hello,
...
My thought is to use an AD account like "authenticated users" and use this for logins so all company users get a login. I would then use specific AD groups I create to map to database roles for specific database related permissions. Is this a good practice? And if so, is "authenticated users" the right group to use to grant everyone a login to the server?

I was also wondering if I will need to remove each login and user before I add the AD group? It would seem if I don't, and then add the AD group any given user would then have two logins? I have also noticed that specific users have a default database set in their login, once switched over to using an AD group for logins how can this be accomplished?

Thanks in advance.

Using Windows Groups instead of individual Logins is indeed a recommended practice
Authenticated Users would work, if you really want ALL AD Users being able to Access SQL Server. That one I wouldn’t consider a “good practice”, but if you really want to do that without exceptions, that’s the way. Otherwise you are better off creating an extra Win Group with ~90% of all Logins inside.
You do not have to remove the Logins before adding the group, but in the long I would advise doing so. Until then all those have 2 different access paths.
For ONE Group-Login you of course can only have ONE “default database” set.
Another reason to use multiple groups..



Thanks for the reply Andreas,

just a couple of other quick questions.

My company wants all users to have default read access to our 3 apps so that's why I thought using "authenticated users" would be the easiest. I will then create a read only database role and also add authenticated users to that. I have created other AD groups that will allow users to perform more restricted tasks in these apps. Unless I'm missing something, when using this method as new employees start they will automatically get read access to our apps and I won't need to do any special "setup" on the SQL server side with respect to security.

Is there any problem with the one group login method only having a single default database? To be honest I'm not really sure what the default database setting is for. If our apps have a connection string with the database name in it, does the default database provide any functionality I'm not aware of?

I also like to have myself and the other sysadmin to have our own logins using our individual AD logins. As we would also belong to the "authenticated users" group will that present any problems?

Thanks,
Tim.
Andreas.Wolter
Andreas.Wolter
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1056
hanrahan_tim (10/12/2013)
Andreas.Wolter (10/10/2013)
hanrahan_tim (10/9/2013)
Hello,
...

Using Windows Groups instead of individual Logins is indeed a recommended practice
Authenticated Users would work, if you really want ALL AD Users being able to Access SQL Server. That one I wouldn’t consider a “good practice”, but if you really want to do that without exceptions, that’s the way. Otherwise you are better off creating an extra Win Group with ~90% of all Logins inside.
You do not have to remove the Logins before adding the group, but in the long I would advise doing so. Until then all those have 2 different access paths.
For ONE Group-Login you of course can only have ONE “default database” set.
Another reason to use multiple groups..


...
(1) I will then create a read only database role and also add authenticated users to that. I have created other AD groups that will allow users to perform more restricted tasks in these apps. Unless I'm missing something, when using this method as new employees start they will automatically get read access to our apps and I won't need to do any special "setup" on the SQL server side with respect to security.

(2) Is there any problem with the one group login method only having a single default database? To be honest I'm not really sure what the default database setting is for. If our apps have a connection string with the database name in it, does the default database provide any functionality I'm not aware of?

(3) I also like to have myself and the other sysadmin to have our own logins using our individual AD logins. As we would also belong to the "authenticated users" group will that present any problems?
...


(1) That is correct. Within SQL Server the Domain Group members are recognized by the Group SID and all role memberships and attached permissions apply.
(2) The default database setting only then has an effect, if a user/application connects without specifying a database name.
(3) No. As long as you don’t play with “Deny” for the “Auth. Users” Group-Login, permissions will just be cumulative and that’s it.

Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Rachel Lee-244397
Rachel Lee-244397
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 246
Hi, not trying to hijack this post, but I have a question I cannot seem to find the answer to.

When a user is a member of multiple AD groups, that have corresponding logins all on the same instance, with different default databases, which 'group' rules?
for example.
ADGroup1, read access to DB1, defaultDB =DB1,
ADGroup2, dbo access to DB2, defaultDB DB2,
ADGroup3, read access to DB3, defaultDB = DB3.

upon connecting, what will be the default db?

thanks for any input...
orlando.ramon
orlando.ramon
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 45
If I have an AD user defined as an SQL LOGIN (AD\john.doe) with sysadmin privilege and is a member of an AD GROUP that is also an SQL SERVER LOGIN with only public privilege. What authority will this user (AD\john.doe) have when connecting to the server ADMIN or PUBLIC?
Smendle
Smendle
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 1073
Rachel Lee-244397 (7/7/2014)
Hi, not trying to hijack this post, but I have a question I cannot seem to find the answer to.

When a user is a member of multiple AD groups, that have corresponding logins all on the same instance, with different default databases, which 'group' rules?
for example.
ADGroup1, read access to DB1, defaultDB =DB1,
ADGroup2, dbo access to DB2, defaultDB DB2,
ADGroup3, read access to DB3, defaultDB = DB3.

upon connecting, what will be the default db?

thanks for any input...


I just tested this and my observations are if no user exactly matches the login and only groups define the login than its alphabetical by database name. This seems to be confirmed by this person HERE
I could not find any official MS documentation on this so again I am not 100% sure.
Smendle
Smendle
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 1073
orlando.ramon (7/1/2016)
If I have an AD user defined as an SQL LOGIN (AD\john.doe) with sysadmin privilege and is a member of an AD GROUP that is also an SQL SERVER LOGIN with only public privilege. What authority will this user (AD\john.doe) have when connecting to the server ADMIN or PUBLIC?


Login permissions are cumulative so if one group gets sysadmin and the other public than that login has sysadmin.

Also of note any deny permissions will overwrite grant permissions as that is the MS paradigm for applying permissions.
(Explicit Deny overrides Grant)
Group1 has Grant on Database1 and Deny on Database2
Group2 has Deny on Database1 and Grant on Database2

Login belongs to Group1 and Group2 will have deny on Database2 and Database1.

I've actually encountered this before with the explanation from the "expert" at the time as it should work because it makes logical sense....my response....whoever said Microsoft was logical?


EDIT***
I should have stated that the fixed sysadmin server role bypasses permission checks as sysadmin has permission to do anything on the server. Everything else about cumulative permissions is correct though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search