6

We want to use Windows Authentification on SQL Server 2008 within Active Directory.

Is it possible to use an AD Group as a LOGIN to the server, but then to map the actual AD USER to a database user?

The idea is to have one AD group which has the basic login access, but then to fine-granulate roles and access rights to a list of single database users.

We also are using row-level-security such that every single user will have access to one single country.

  • 2
    If you need to set up individual permissions, what is the point of using the group? Usually this is so that you can apply permissions en masse when you don't want to set the same set of permissions repeatedly to a bunch of users. – Aaron Bertrand Aug 8 '13 at 13:14
  • If you have a base set of permissions that you want to apply across the board, you can do so using a custom database-level role (and in SQL Server 2012 and above, a custom server-level role), rather than try to cram that into an AD group. Otherwise please elaborate on what you want the AD group to do for you if not to simplify permissions. – Aaron Bertrand Aug 8 '13 at 13:24
  • Although I responded with with a 'yes, sure', I do agree with Aaron that breaking out the users from the group at least misses some of benefits of using groups. – RLF Aug 8 '13 at 13:41
  • Thanks a lot for the discussion on this topic! I think I can re-define my question a little bit further now: We have a group of users which all have access to the same list of stored procedures. Within the SP's, we are maintaining a row level security. Our users are Country Managers, and everybody should see just "his/her" Countries. So probably the concept should be to create just ONE database user, which is mapped to ONE Login (=AD Group), but then, we would need to somehow get the actual 'PERSON' (=the AD User) in the stored procedure. Is that possible, and is that better? – SQLGeorge Aug 8 '13 at 16:28
  • Yes, that is what I was trying to describe in my potential answer. If the AD Group is made a USER in the database, everyone in the group (barring DENY permission) will have the same rights. Their USERs by default will be named "Domain\Loginname" for their individual login and can be used for row-level security. – RLF Aug 8 '13 at 17:45
5

First of all, the way you are wanting to do it won't really work. You can't have one login and multiple users on a single database. If you try you get the following error:

Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.

What you really should do is create your generic group and create a login and users for it. This will provide the basic permissions for everyone. Next create an AD group for each country you are dealing with. Add your individuals to these groups. Then create logins and users for those groups providing them the individual access you want.

The benefit here is that if someone leaves the company all you have to do is move them out of the AD group and move someone else in. Or if a second person (or more) has to be added to a country it's a trivial thing.

EDIT: For example:

  • AD Group : DOMAIN\Sales

    • Users: Bob and Joe
  • AD Group: DOMAIN\Sales_US

    • User: Bob
  • AD Group: DOMAIN\Sales_Canada

    • User: Joe

All three groups have SQL Logins created All three groups also have a user created on the Sales database.

The DOMAIN\Sales group is granted SELECT access to tables Inventory and InventoryPricing The DOMAIN\Sales_US group is granted SELECT permission on the view vw_Sales_US and execute on the stored procedures usp_Add_US_Sale and usp_Edit_US_Sale The DOMAIN\Sales_Canada group is granted SELECT permission on the view vw_Sales_Canada and execute on the stored procedures usp_Add_Canada_Sale and usp_Edit_Canada_Sale

This gives Bob access to the US information and the shared information and Joe access to the Canada information and the shared information.

Later on down the line Mary replaces Bob on US sales and since Canada sales have increased dramatically Bob has joined Joe on Canada sales.

The only changes that need to be made are removing Bob from DOMAIN\Sales_US and adding Mary and adding Bob to DOMAIN\Sales_Canada

  • Of course if you put all of the users under a common AD group (e.g. DOMAIN\Canada), create a server-level login for that, and then make a database user (e.g. Canada) mapped to that login, you still won't be able to apply permissions more granularly within that set of users, say if you wanted to let Corey Hart update the sunglasses table, but not Celine Dion. Never mind auditing etc. – Aaron Bertrand Aug 8 '13 at 14:22
  • Of course, if the AD Group is added to the server, then to each database involved, individual logins would appear as the 'domian\individuallogin' user for SUSER_SNAME and USER_NAME unless an individual login has an explicitly created sys.database_principal using a different user name. Therefore, the row level permissions (however they are implemented) could still distiguish between individuals. – RLF Aug 8 '13 at 15:00
  • @AaronBertrand That is why I suggested using the generic group only for group permissions. For example if everyone needs access to a given table. For more granule permissions you would use additional AD groups. – Kenneth Fisher Aug 8 '13 at 15:14
  • OK, the original question is unclear and it sounds like for most of this the whole purpose of a single group is defeated if you need to create a bunch of groups or individual users to get the permission granularity you need. – Aaron Bertrand Aug 8 '13 at 15:20
  • @AaronBertrand In general I've found it easier to have a larger group that deals with shared permissions and then more granular groups to deal with more granular permissions. A good example is a "Developers" group that grants access to a developers only database while having application specific groups granting access to the application specific databases. – Kenneth Fisher Aug 8 '13 at 15:31
1

Yes, you may use an AD Windows Group as a LOGIN to the server and then map the individual AD Windows Logins to database USERs in the approriate databases. Since the Windows Group LOGIN only adds rights to access the SQL Server and any databases with the guest user enabled (master, msdb, tempdb) then no rights are conferred to other databases.

Therefore, individual users can be assigned to different databases since the USER it what confers rights to the database.

(Of course, if you map the Group to the database, then everybody in the group gets rights to the database.)

Your row-level security is based on users I assume. If you add the Windows Group to a database, by default (unless you explicitly name them) the Windows Group members will have Users named like 'domain\login'.

  • But isn't it the case that you can only map one database user, per database, to the login representing the AD group? Inside of one database (which is the only context where row-level security makes sense), you couldn't map more than one unique user to the Windows group login. – Aaron Bertrand Aug 8 '13 at 14:19
  • If that is what the poster meant, then your are correct. But that is not what I understood the question to be. What I read was: 1 - Use a Windows Group to grant access to the server, 2 - Use the Windows Logins that are contained in that group to individually create database Users. (I added a couple of italicized phrases to clariy what I was saying.) And, Yes, that greatly undermines the value of using the Windows Group. – RLF Aug 8 '13 at 14:41
1

I know this is an old topic, but I was effectively looking to do the same thing and thought I'd share my solution.

It is true that by default CREATE USER requires that you assign that to a server principal (login). However, you can specify "WITHOUT LOGIN" on the user you wish to create (in this example, the individual group member for whom I want to assign specific permission). In this case, I still specify my database principal as a windows login with the domain and .

USE master
GO
--Create a server login for the AD group.
CREATE LOGIN [Domain\MY AD Group] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--Switch context to the database that requires granular control
USE myDB
GO
--Add a database user where the ID matches the windows login and where the user is a member of the above group.
CREATE USER [Domain\IndividualUser] WITHOUT LOGIN 
GO

I agree that a better implementation is still to assign individual users to specific groups and assign permission that way (in the case of row-level security... stands to reason one would want to change users that can access those rows. Ensures consistency if you do it with a group, or at least a role, should new users come on or existing users leave).

In my case, I was looking to be able to assign users the ability to maintain their own environment variables in SSISDB (which is a 2012 feature, I know, but this security implementation is available in 08 and 08R2) where the only person who has any business editing those variables is the individual user, such as their password to a data source that is not windows-authenticated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.