Getting Active Directory Groups in T-SQL

Home » C# » Server Side Programming » SQL and Databases » Getting Active Directory Groups in T-SQL
Photograph of a SSD Drive Array

I had a problem with a C# Winforms application recently that uses EF and some ADO.NET code to access SQL/Server. The customer wanted to use Active Directory to set up the users for this system.

There are two ways of doing this:

  1. Define the AD users in SQL Server Management Studio (SSMS), assigning these to the roles that are defined in the database, e.g. User, Manager, Admin etc.
  2. Define the AD users and AD groups in Active Directory, and then just map the groups to roles in SSMS.

The customer wanted to use the second method and this had me confused for a bit, because it means that the users are not defined in the database, so sp_helpuser will come up with an error if you try to use it.

In the past I had used my own views to do this kind of thing but its dependant on the version of SQL/Server because my views were based on SQL/Server system views. There had to be an easier way.

What I came up with was to use sp_helprolemember and filter for the current user (in case the customer wanted to use AD users in SSMS), then use two calls, one to xp_logininfo, then pass the results to sp_helpuser. So to summarise the plan is to do something like this:

EXEC sys.sp_helprolemember @rolename = NULL -- sysname

-- filter using the user id for just the roles we are interested in
-- does not return anything unless you set AD users up in SSMS

-- returns MyMachine\TestGroup as a group
EXEC sys.xp_logininfo @acctname = 'MyMachine\phil', -- sysname
    @option = 'ALL' -- varchar(10)

-- get the roles that the MyMachine\TestGroup is assigned to, which is what we are trying to get in the first place
EXEC sys.sp_helpuser @name_in_db = 'MyMachine\TestGroup'

The above code will not work as such, its just an explanation of the approach. I’ve included the actual code I have used in the app, which is part of a C# database helper class:

        
public static string[] GetRolesForLoggedOnUser(string userName)
{
    var roles = new ArrayList();

    using (var con = new SqlConnection(_currentConnectionString))
    {
        con.Open();
#if DEBUG
        CurrentLogger.Trace("Finding roles for user {0}", userName);
#endif
        // find out the roles for this user
        string sql = "sp_helprolemember";
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                var dbRole = (string)rdr["DbRole"];
                var memberName = (string)rdr["MemberName"];

                if (String.Equals(memberName, userName, StringComparison.OrdinalIgnoreCase))
                    AddRole(roles, dbRole);
            }
            rdr.Close();
        };

        // find out the ad groups for this user
        var permissionPaths = new ArrayList();
        sql = "xp_logininfo";
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@acctname", userName);
            cmd.Parameters.AddWithValue("@option", "ALL");
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                if (rdr["permission path"] == DBNull.Value)
                    continue;

                var permissionPath = (string)rdr["permission path"];

                AddRole(roles, permissionPath);

                permissionPaths.Add(permissionPath);
            }
            rdr.Close();
        }

        // now look at all those groups and see if they have anything of interest
        sql = "sp_helpuser";
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name_in_db", SqlDbType.VarChar);

            foreach (var permissionPath in permissionPaths)
            {
                cmd.Parameters["@name_in_db"].Value = permissionPath;
                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var roleName = (string) rdr["RoleName"];
                    AddRole(roles, roleName);
                }
                rdr.Close();
            }
        }
        con.Close();
    }
    return (string[])roles.ToArray(typeof(string));
}

private static void AddRole(ArrayList roles, string roleToAdd)
{
#if DEBUG 
    CurrentLogger.Trace("role = {0}",roleToAdd);
#endif
    roles.Add(roleToAdd);
}

I am thinking that an alternative might be just to call sp_helpuser and if an error occurs then the user doesn’t exist and seeing as we are connected to the database, then its reasonable to carry out the second and third steps above. However I’m happy with this approach. Just trying to work out if the final step needs a try/catch as I write, just in case something comes out of xp_logininfo that might ruin my day.

Update: IS_MEMBER
Since writing the original post I have come across something that might be easier for detecting SQL roles and AD groups, the T-SQL function IS_MEMBER.

This function returns 1 if the user is a member of the role, 0 if the role is valid but the user is not a member, null otherwise. The C# code then becomes:

public static string[] GetRolesForLoggedOnUserTake4(string userName)
{
    var roles = new ArrayList();

    using (var con = new SqlConnection(_currentConnectionString))
    {
        con.Open();

        const string sql = "SELECT is_member(@role)";
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@role", SqlDbType.VarChar);

            foreach (var role in new[]{"Role1","Role2","etc"})
            {
               cmd.Parameters["@role"].Value = role;
                var result = cmd.ExecuteScalar();
                if (result != DBNull.Value && result != 0)
                    AddRole(roles, role);
            }
        }
    }
    return (string[])roles.ToArray(typeof(string));
}

If your app understands SQL roles internally, but has to be able to handle AD groups (typically so DBA’s don’t have to do anything to set up users, then some translation may be required. You could pass the total list of roles needed in as key value pairs – role name, ad group name, or role name, role name. The above code becomes:

public static string[] GetRolesForLoggedOnUserTake(string userName, 
            IEnumerable> allRoles)
{
    var roles = new ArrayList();
    using (var con = new SqlConnection(_currentConnectionString))
    {
        con.Open();

        const string sql = "SELECT IS_MEMBER(@role)";

        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@role", SqlDbType.VarChar);

            foreach (var role in allRoles)
            {
                cmd.Parameters["@role"].Value = role.Value ?? role.Key;

                var result = cmd.ExecuteScalar();
                if (result != DBNull.Value && (int)result == 1)
                    AddRole(roles, role.Key);
            }
        }
    }

    return (string[])roles.ToArray(typeof(string));
}

There are also worries about the user not having a schema if defined via an AD group in versions of SQL/Server prior to 2012, which might have temporary table/processing implications. If these problems affect you, then defining the users in Active Directory and the mappings in SQL/Server management studio might be the best option (i.e. do not use Active Directory Groups).



About Phil

I have been working as a software developer since 1983. This blog could have been called "From Fortran 77, C and Cobol to C# in 20 (not so) easy years", but it doesn't sound quite right somehow. Besides I'm talking about what's happened since 2003, not before!

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
Be a Great Product Owner: Six Things Teams and Scrum Masters Need

Learn six ways effective product owners ensure their teams’ success. [...]

What Happens When During a Sprint

Succeeding with Scrum is easier when you know when and why to conduct each of the Scrum events during the sprint. [...]

What Are Agile Story Points?

Story points are perhaps the most misunderstood topic in agile. Story points are not based on just one factor--such as complexity, as is often mistakenly claimed. Instead, story points are based on a combination of factors. [...]

Don’t Equate Story Points to Hours

I’ve been quite adamant lately that story points are about time, specifically effort. But that does not mean you should say something like, “One story point = eight hours.” Doing this obviates the main reason to use story points in the... [...]

Epics, Features and User Stories

I've been getting more and more emails lately from people confused about the difference between "user stories", "epics" and "features." So I thought this month we'd return and cover some basic--but very helpful--territory by explaining those terms. First, the terms don't matter that much. These are not terms with important specific meanings like "pointer" to a programmer or "collateralized debt obligation" to whomever it is that's important. [...]

- Scott Hanselman
Use your own user @ domain for Mastodon discoverability with the WebFinger Protocol without hosting a server

Mastodon is a free, open-source social networking service that is decentralized and distributed. It was created in 2016 as an alternative to centralized social media platforms such as Twitter and Facebook. One of the key features of Mastodon is the use of the WebFinger protocol, which allows users to discover and access information about other users on the Mastodon network. WebFinger is a simple HTTP-based protocol that enables a user to discover information about other users or resources on the internet by using their email address or other identifying information. The WebFinger protocol is important for Mastodon because it enables… [...]

- Scott Hanselman
I got tired

I have been blogging here for the last 20 years. Every Tuesday and Thursday, quite consistently, for two decades. But last year, without planning it, I got tired and stopped. Not sure why. It didn't correspond with any life events. Nothing interesting or notable happened. I just stopped. I did find joy on TikTok and amassed a small group of like-minded followers there. I enjoy my YouTube as well, and my weekly podcast is going strong with nearly 900 (!) episodes of interviews with cool people. I've also recently started posting on Mastodon (a fediverse (federated universe)) Twitter alternative that… [...]

- Scott Hanselman
Using Home Assistant to integrate a Unifi Protect G4 Doorbell and Amazon Alexa to announce visitors

I am not a Home Assistant expert, but it's clearly a massive and powerful ecosystem. I've interviewed the creator of Home Assistant on my podcast and I encourage you to check out that chat. Home Assistant can quickly become a hobby that overwhelms you. Every object (entity) in your house that is even remotely connected can become programmable. Everything. Even people! You can declare that any name:value pair that (for example) your phone can expose can be consumable by Home Assistant. Questions like "is Scott home" or "what's Scott's phone battery" can be associated with Scott the Entity in the… [...]

- Scott Hanselman
JavaScript and TypeScript Projects with React, Angular, or Vue in Visual Studio 2022 with or without .NET

I was reading Gabby's blog post about the new TypeScript/JavaScript project experience in Visual Studio 2022. You should read the docs on JavaScript and TypeScript in Visual Studio 2022. If you're used to ASP.NET apps when you think about apps that are JavaScript heavy, "front end apps" or TypeScript focused, it can be confusing as to "where does .NET fit in?" You need to consider the responsibilities of your various projects or subsystems and the multiple totally valid ways you can build a web site or web app. Let's consider just a few: An ASP.NET Web app that renders HTML… [...]

- Scott Hanselman
A Nightscout Segment for OhMyPosh shows my realtime Blood Sugar readings in my Git Prompt

I've talked about how I love a nice pretty prompt in my Windows Terminal and made videos showing in detail how to do it. I've also worked with my buddy TooTallNate to put my real-time blood sugar into a bash or PowerShell prompt, but this was back in 2017. Now that I'm "Team OhMyPosh" I have been meaning to write a Nightscout "segment" for my prompt. Nightscout is an open source self-hosted (there are commercial hosts also like T1Pal) website and API for remote display of real-time and near-real-time glucose readings for Diabetics like myself. Since my body has an… [...]

Meta