Enumerate Windows Group Membership within SQL Server

• Stephen_W_Dodd

  SSCommitted

  Points: 1648

  More actions

  August 30, 2011 at 1:40 pm

  #99415

  Hi Gurus,

  This might be a fun one.

  Environment: XP Pro (client), SQL Server 2008 R2 (database engine) on Windows Server 2008

  I'm running up against a policy, enforced by an outside entity, where we can't individually add logins to a database; users have to belong to a Windows group. So under the "Users" folder in SSMS for the database in question, you might see "Windows_Group_R", "Windows_Group_W", etc.

  My original design for determining whether a user belonged to a database role (custom in this case) was to use a view that enumerated the groups and users belonging to those groups. Example code:

  SELECT sys.database_role_members.role_principal_id, sys.database_principals.name AS role_name, sys.database_role_members.member_principal_id,

  SUBSTRING(Member.name, PATINDEX('%\%', Member.name) + 1, LEN(Member.name) - PATINDEX('%\%', Member.name) + 1) AS HID

  FROM sys.database_role_members INNER JOIN

  sys.database_principals ON sys.database_role_members.role_principal_id = sys.database_principals.principal_id INNER JOIN

  sys.database_principals AS Member ON sys.database_role_members.member_principal_id = Member.principal_id

  WHERE (sys.database_role_members.role_principal_id > 0) AND (sys.database_role_members.role_principal_id < 16384) AND

  (sys.database_principals.type = 'R')

  I can't use this technique when the "Users" are Windows groups; I can't "see" into the Windows group to look at its members. Anybody have an idea / solution? Do I have to use a Windows API call? I don't know how to do that from within a view or stored procedure.

  Any suggestions / help much appreciated.

  Steve

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 30, 2011 at 1:52 pm

  #1375220

  there's an extended stored proc that can help, i think

  here's an example of the syntax:

  EXEC master..xp_logininfo

  @acctname = 'mydomain\lowell',

  @option = 'all' -- Show all paths a specific user gets his auth from

  go

  EXEC master..xp_logininfo

  @acctname = 'mydomain\authenticatedusers',

  @option = 'members' -- show group members

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Stephen_W_Dodd

  SSCommitted

  Points: 1648

  More actions

  August 30, 2011 at 1:55 pm

  #1375222

  Thank you for the reply, Lowell.

  In the production environment, that (and probably those) extended procedure call(s) are not available (permission denied).