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:

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:

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:

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:

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

The iterative and incremental nature of agile makes an agile approach seemingly less compatible with [...]

- mike@mountaingoatsoftware.com

Velocity can be great for predicting how much a team can deliver in a given period. But it needs to [...]

- mike@mountaingoatsoftware.com

Succeeding with agile isn’t just about knowing where to start, it’s about knowing where to go next—w [...]

- mike@mountaingoatsoftware.com

Here’s what to do when facing a complex user story that cannot be split but is too large for one spr [...]

- mike@mountaingoatsoftware.com

A lot of organizations claim to be agile. Here’s a quick way to see if they really are. [...]

- Scott Hanselman

ASP.NET Core 2.2 is out and released and upgrading my podcast site was very easy. Once I had it upda [...]

- Scott Hanselman

Well crap. I was typing really fast and got a squiggly, so I right-clicked on it and rather than sel [...]

- Scott Hanselman

Buckle up friends! Microsoft is open sourcing WPF, Windows Forms (winforms), and WinUI, so the three [...]

- Scott Hanselman

Naming things is hard. I've talked before about the term "evangelism" and my dislike [...]

- Scott Hanselman

Hey friends! This is my FIFTH year doing a list of Great STEM Christmas Toys for Kids! Can you belie [...]

Meta