Replacing individual logins with AD Groups potential pitfalls?

Question

Replacing individual logins with AD Groups potential pitfalls?

hanrahan_tim

Right there with Babe

Points: 717
More actions
October 9, 2013 at 11:21 am

#299029

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Andreas.Wolter SSCommitted Points: 1780 More actions · Answer 1

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 Right there with Babe Points: 717 More actions · Answer 2

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 SSCommitted Points: 1780 More actions · Answer 3

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 SSC Veteran Points: 234 More actions · Answer 4

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 SSC Rookie Points: 47 More actions · Answer 5

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 Hall of Fame Points: 3969 More actions · Answer 6

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 Hall of Fame Points: 3969 More actions · Answer 7

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.