Getting Active Directory Groups in T-SQL, Part 2

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:

SELECT (IS_ROLEMEMBER('MyRole') | IS_SRVROLEMEMBER('sysadmin')) AS IsInRole

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:

SELECT U.name AS username, U.type_desc as usertypedesc, RNames.name AS rolename, RNames.type_desc AS roletypedesc
FROM sys.database_principals AS U
JOIN sys.database_role_members AS R
    ON U.principal_id = R.member_principal_id
JOIN sys.database_principals AS RNames
 ON r.role_principal_id = RNames.principal_id

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.