Sql-server – Domain group as single login, but still many different database users

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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

Related Question