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...