SQL Server Performance Forum – Threads Archive

Active Directory Groups

I have to admit my brain is not working today and I need some quick answers. I have Windows Authentication setup and I have two AD groups named ADSQL1 and ADSQL2. I also have a user named ADDev in both groups. both SQL1 and SQL2 have access to my database named MyDB but with different rights. OK, when user Dev sign into an application or Query Analyzer,
1. how can I tell Dev’s AD group name? when you run sp_who2 or suser_sname, you only see ADDev…
2. how SQL server adjust ADDev’s right since Dev is in both groups? thanks!

question 2 is somehow related to question 1. basically where is the connection information stored once user is authenticated by SQL Server for AD users?

Have you tried with XP_LOGININFO in this case?
BOL explains about impersonation & authentication

quote:
Under delegation, the instance of SQL Server to which a Windows user has connected by using Windows authentication impersonates that user when communicating with another instance of SQL Server or with a SQL Server provider. This second instance or provider can be on the same computer or on a remote computer within the same Windows domain as the first instance. Security account delegation may be required when you access providers on a different computer for running distributed queries. Enabling delegation for distributed queries involves configuration changes within both SQL Server and Active Directory.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Satya,
it looks xp_logininfo only works for names already in the database.
xp_logininfo ADSQL1 world work but not xp_logininfo ADDev.

Try
select * from OPENQUERY (LDAP, ‘
SELECT cn, adspath, memberOf
FROM ”LDAP://DC=company,DC=com”
WHERE memberOf = ”cn=development,dc=company,dc=com”
‘)
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

thanks Satya.

So did you get the desired results? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I haven’t tried this but I think it should work. I’m setting up some login groups from AD and application roles. I need to map the user to a proper application role when user signon to the spplication without a pwd (single signon). at the begining I thought I need to query the database to get the user’s proper group (just like to run sp_helplogins to get role info for a regular db login – sql security). on the other hand, it (your post) just reminded me I can query AD on the client side and then set the user to the proper application role. for others who are interested in the topic. another link with more details
http://blogs.msdn.com/euanga/archiv…i-query-active-directory-from-sql-server.aspx
and lots of links talking about how to do it from .net app. thanks

]]>