Search code examples
sql-serverdatabasednsroleactive-directory-group

How to retrieve SQL Server database roles for an Active Directory domain group

I have the following environment:

  • Microsoft SQL Server Enterprise 64bit version 11.0.5623.0 running on Microsoft Windows NT 6.1 (7601)

  • I have a domain called MyDomain, all end users are member of this domain, e.g. user Donald from the USA and user Fritz from Germany

  • For each country I have a dedicated domain group e.g. MyDomain_Sales_USA group contains the US user Donald, MyDomain_Sales_GER contains the German user Fritz

  • I have one SQL Server instance in the MyDomain with a Sales database.

  • The Sales database contains a table called Products that is shown here:

    enter image description here

  • End users access the Products table with their domain account (single sign on) to read data

The business case:

  • USA users that are members of the domain group MyDomain_Sales_USA only see the products with Country = USA, similarly German users only see the Germany products.
  • users are added and removed from the two domain groups frequently
  • The SQL Server admins only want to configure the domain groups. No domain users are configured in the SQL Server.

My approach:

  1. Using SQL Server Management Studio I added the domain groups MyDomain_Sales_USA and MyDomain_Sales_GER to the SQL Server Security\Logins node and mapped each group to the Sales database.

  2. I created two database roles in the Sales database, SalesRole_USA and SalesRole_GER. Using the database role properties dialog I added the MyDomain_Sales_USA as a member to the SalesRole_USA role and the MyDomain_Sales_GER to the SalesRole_GER role

  3. I created a view in products database with the following select statement

    SELECT * 
FROM Products 
WHERE Country = GetCurrentUserCountry()

Here's my question:

How can I in the GetCurrentUserCountry() function to determine to which database role the connected user belongs?

In other words: if Donald executes the select statement above then the function must return 'USA', if Fritz executes the statement then the function must return 'GER'

My expectation is the following:

  1. SQL Server grants access to Donald and Fritz because their domain groups are registered and mapped to the Sales database

  2. So it must be possible to retrieve the database roles for the connected user

Solution

  • In the GetCurrentUserCountry() function I used the system function IS_MEMBER('MyDomain_Sales_USA') and IS_MEMBER('MyDomain_Sales_GER'), this works fine but has a small disadvantage cause I have to update the function everytime a new country group is created.