Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replacing individual logins with AD Groups potential pitfalls? Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:43 AM
Points: 15, Visits: 120
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.
Post #1503215
Posted Thursday, October 10, 2013 9:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:33 AM
Points: 153, Visits: 977
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
Post #1503681
Posted Saturday, October 12, 2013 11:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:43 AM
Points: 15, Visits: 120
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.
Post #1504221
Posted Saturday, October 12, 2013 8:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:33 AM
Points: 153, Visits: 977
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
Post #1504240
Posted Monday, July 7, 2014 8:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:23 PM
Points: 14, Visits: 131
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...
Post #1590181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse