What i meant was, how does any one see the users inside a Active Directory / NT Group. For example we have a group named BI_AD_GROUP with domain as SPONGBOB, so the group can be accessed as [SPONGBOB\BI_AD_GROUP].
you must have sysadmin Privileges and attitude to view this info.
you might be gearing up for a complex T-SQL, which will do the work, but instead it is just a simple one liner code.
xp_logininfo ‘SPONGBOB\BI_AD_GROUP’,’members’
The Group has to already exist in SQL Server as a login, to view its members.
This SQL will give you the list of users in the Group something like this:
| account name | type | privilege | mapped login name | permission path |
| SPONGEBOB\USER1 | user | user | SPONGEBOB\USER1 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER2 | user | user | SPONGEBOB\USER2 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER3 | user | user | SPONGEBOB\USER3 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER4 | user | user | SPONGEBOB\USER4 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER5 | user | user | SPONGEBOB\USER5 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER6 | user | user | SPONGEBOB\USER6 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER7 | user | user | SPONGEBOB\USER7 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER8 | user | user | SPONGEBOB\USER8 | SPONGBOB\BI_AD_GROUP |
Now, the greed never ends. we want to view all the users in all the AD Groups..
I am going to get all the nt groups in a temp table and go from there.
select name into #getxplogininfo from master..syslogins where isntgroup=1
select * from #getxplogininfo
now you can write a cursor based on the info or something like this:
SELECT ‘xp_logininfo ‘+””+name+””+’,'+””+’members’+”” from #getxplogininfo
Copy and paste the result in a new query windows and execute or however you want to do it.
Loading ...