Getting Active Directory Groups in T-SQL, Part 2

Home » SQL and Databases » Getting Active Directory Groups in T-SQL, Part 2
Photograph of a SSD Drive Array

Following on from my recent post on the subject, I was unhappy with the conclusion of that post, because while I was offering many ways to solve the problem, I wasn’t happy with the code. Connecting to a database should be simple? Well to be fair to Sql/Server it is, but there are numerous ways to work out if a user is in a role, particularly if AD groups are being used as well.

It turns out that the T-SQL function IS_ROLEMEMBER is a pretty good solution apart from one circumstance – that is if the user is also a member of a server role as well, in which case it will return 0 when the user is actually in the role (see the IS_ROLEMEMBER erroneously returns 0 for database role members for further details).

So a solution might be to say something like:

However if you have a lot of roles to check this might be slow and we want user logins to be as fast as possible. Right?

The other alternative I found was to use a query on sys.database_principals, something like this:

You can always add U.name = ‘myuser’ in the where clause.

Thats it, I’m just about done with user authorisation in SQL/server for now. I’ve personally posted about 6 ways to do it. As most of my development is web, this is not something I come across very often. Despite the previous post I am going with one of these two methods for now.

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

It can sometimes be a challenge to get people to attend and then participate in sprint reviews. Here [...]

- mike@mountaingoatsoftware.com

The standard “As a...I...so that…” user story template has stood the test of time. Here’s why each o [...]

- mike@mountaingoatsoftware.com

From helping teams understand the boundaries of self organization to creating safety around things l [...]

- mike@mountaingoatsoftware.com

Your team is probably spending too much time in sprint planning meetings. Here’s how to spend less t [...]

- mike@mountaingoatsoftware.com

Debating between Scrum and Kanban? Guest author Brendan Wovchko offers five advantages Kanban has ov [...]

- Scott Hanselman

There's been a lot of folks, myself included, who have tried to install VS Code on the Raspberr [...]

- Scott Hanselman

I don’t speak in hyperbole very often, and I want to make sure that you all understand what a big de [...]

- Scott Hanselman

I recently needed to refactor my podcast site which is written in ASP.NET Core 2.2 and running in Az [...]

- Scott Hanselman

.NET code (C#, VB, F#, etc) compiles (for the most part) into Intermediate Language (IL) and then ma [...]

- Scott Hanselman

Almost ten years ago I posted abut the SpaceTec SpaceOrb 360 Controller and that was 15 years after [...]

Meta