Login Group with SQL Server Authentication

Question

Login Group with SQL Server Authentication

AER

SSCrazy

Points: 2700
More actions
December 14, 2011 at 9:42 am

#249778

Hi everybody,
Can I create a new SQL Server login with SQL Server Authentication and assign few individual Users (create a Group) that are the Members of Domain to this login? If there is no way to do it, then is there a walk around?
I'm in SQL 2008 Enterprise environment.
Thank you.
Alex

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

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

AER (12/14/2011)
Hi everybody,
Can I create a new SQL Server login with SQL Server Authentication and assign few individual Users (create a Group) that are the Members of Domain to this login? If there is no way to do it, then is there a walk around?
I'm in SQL 2008 Enterprise environment.
Thank you.
Alex

You are on the right path I think you mistaken in where the group is created. It is easiest to have an Active Directory group created and then THAT group is added to SQL. Otherwise you have to use roles at the database level which is kind of a pain if you are always assigning the same rights to the same people..

CEWII

AER SSCrazy Points: 2700 More actions · Answer 2

Thanks Elliott,

I know that you can add a Group from the Active Directory with a Windows Authentication; this is not a problem.

The problem is that a SQL DBA not always a member of AD Admin Group.

So in order to control that, you need to request from the AD Admins to add a certain group of employees to the Active Directory.

And then you need to request modifying it every time you want to add or drop somebody from this group.

The approval process can take time especcially in the corp environments where these processes sometimes take a long time.

Rather if a SQL DBA would be able to add his own group of Users to the Login then he would be able to control this quick and reliable.

I guess the question here: is a SQL DBA suppose to be a member of AD Admin group?

If not, then how should the Security model be set for the DBA to be able to quickly assign different groups of employees different roles in DB?

Thank you.

The response to this post is greatly appreciated.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

To answer part of your question, SQL DBA may or may not be part of AD Admin group. I am not a part of AD Admin group, but I am sysadmin on the SQL box.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

AER SSCrazy Points: 2700 More actions · Answer 4

Thanks Bru,

There is no question that a SQL DBA is a sysadmin on the SQL Server.

But according to Elliott's response (and my research) sysadmin rights still would not allow you to create your own SQL group and assign the appropriate permissions to it. You can either add an AD Group or get this accomplished through the roles instruments that is a pain.

That's where my question comes:

If there is no other way to accomplish my task, then is the SQL DBA supposed to be a member of AD Admin group? Or if there another way around?

I just need an advice to my question.

Thank you.

Alex

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

AER (12/14/2011)
The problem is that a SQL DBA not always a member of AD Admin Group.

I don't see why this is such a problem.. The most common way to handle this is that a productoin SQL DBA group is created in AD, and realistically the membership isn't changing often.. hiring and firing is about it.. Then this group is added to ALL SQL servers as a sysadmin. While this means ALL DBAs have access to all prod servers by rule you leave other peoples boxes alone in most cases..

So in order to control that, you need to request from the AD Admins to add a certain group of employees to the Active Directory.
And then you need to request modifying it every time you want to add or drop somebody from this group.
The approval process can take time especcially in the corp environments where these processes sometimes take a long time.

I understand the point but the functionality you are looking for does not exist in SQL at this time. Push for a streamlining of the permissions process. I've worked in small to large to LARGE! organizations and one of the largest did it the best.. They had a system that was different than their regular change control. A request was made the person who could approve it was notified and all they had to do was click approve.. Then we were made aware of it. OR the AD people were. Once approved you needed to get it done in a reasonable time. Since the functionality isn't available there is really no way to simulate what you appear to want.

Rather if a SQL DBA would be able to add his own group of Users to the Login then he would be able to control this quick and reliable.

If the group is added to a specific OU in AD when the SQL Admins can be made admins of that OU and they can then grant the membership directly. Whether your AD people agree to that is a question.

I guess the question here: is a SQL DBA suppose to be a member of AD Admin group?

Like AD Adminis? I'd go with NO, I generally don't want that much permission..

If not, then how should the Security model be set for the DBA to be able to quickly assign different groups of employees different roles in DB?
Thank you.

The SQL admin really only has two options by default. Add a AD group and assign permissions to the database and DB objects OR add each individual user and then assign permissions to the database and DB objects. The DB permissions can be through roles but you still have to assign them for EVERY user. There is not other way in SQL up to SQL 2008R2. In SQL 2012 I *think* there may be server level groups, but you would still have to add each individual user and then assign them to that group. I would not recommend an upgrade to SQL 2012 next year just for this..

While I understand your concern and problem, the best answer is STILL to use AD groups and try to get management on board for a change in how permissions are managed. They need to understand the issues the way the current process works and you need to be able to present a solution instead of "this doesn't work, lets change it"..

CEWII

AER SSCrazy Points: 2700 More actions · Answer 6

Thanks for your reponse Elliott, this is fare enough.

Any other suggestions or ideas would be greatly appreciated.

Alex

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 7

You are very welcome. I wish I could tell you what you want to hear but it just isn't possible that way yet. Truly the best option is to reform the problem process which makes AD much more useful and I push for AD whenever it makes sense..

I hope it works out for you.

CEWII