Accessing the Windows ACL from Transact-SQL

Hi everybody,
I have a dilemna with a project I am developing.
I am deploying the project to a Windows Small Business Server 2003 network, so as part of the security I have created a number of database roles (manager, secretary, bookkeeper, facilitator) and linked a Windows Group (DOMAIN\Facilitators, DOMAIN\Secretaries, etc.) to each of these roles. I have found that this model is by far the easiest way to administer access to my application.
My problem lies now in the fact that I need to assign a "referral" (a row in a table) to a facilitator. I have tried creating a staff table, with a boolean facilitator field and a UserName field that refers to the SYSTEM_USER function, but this means additional administration and I'm not satisfied that this is the best solution.
Is there a way to, using Transact-SQL, access a list of Windows Users from a specific Windows Group? ie. accessing domain ACLs? As I'm using SQL Server 2005, I do have the option of creating a CLR assemby to access this functionality, and I'm satisfied that I can assign the appropriate permissions for this information to be accessible, but if the information is directly accessible in tsql I would dearly love to know about it.
Thanks for your time guys.
Nathan Brittain
Utamic Software Pty Ltd

You can query Active directory by setting it up as a linked server. i.e.

Exec sp_addlinkedserver 'ADSI' , 'Active Directory servcies 2.5' , 'ADSDSOObject' , 'adsdatasource'

Then to query

Select * from openquery

(ADSI , 'Select givenName , sn , userPrincipleName From "LDAP://OU = Development , DC = admin , DC = contoso , DC = msft"')

|||Thanks for that! I'll have to try this.
After considering the problem further, I guess I should clarify. Is there a way to find a list of Windows User accounts that are directly or indirectly (through Windows Group membership) assigned to a database role?
Again, the work could be put in to clr programming or using a linked server, but if there is a sys.* view available that can provide me this information directly it would be much easier.
I'll be looking into this further myself anyway and posting an answer if I can find one, but if anyone has suggestions... well, thanks!
|||No, there is really no easy way to do this. You can use xp_logininfo with the Windows group name and members as second parameter to get the next level information for the Windows group. But this is about what you can do. To recurse through all the groups and users you will have to write your own code. For your problem, this particular SP will help. Note that the permissions required to run this SP has been changed in SQL Server 2005 for security reasons (only sysadmin).|||

Hello,

I read about the limitations on running the Xp_logininfo in SQL 2005 (just as you say). In fact, from: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ee7162b5-e11f-4a0e-a09c-1878814dbbbd.htm

I read

Note:

In earlier versions of Microsoft SQL Server, permission to execute xp_logininfo can be granted to users. In SQL Server 2005, xp_logininfo can be run only by an administrator.

Nevertheless, I made a test and succeeded to grant the rights to a user which is not a sysadmin, have you any idea on the reason? Is it a bug which will be fixed in the next SP or is there an error in the documentation?

Thank you very much.

Vania

|||

I have a small question regarding the same. I am not able to get the
members of the domain group inside another domain group. As per your
suggestion, I have tried to retrive recursively. But I am able to get the
members, if I register the domain group in SQL Server. FYI, I neither
have the Active directory server name nor the access.

Could you please advise how we can get the members of domain group
without registering in SQL Server? TIA...