Checking user's AD Group

Question

Checking user's AD Group

dwilliscp

SSCrazy Eights

Points: 9263
More actions
July 17, 2014 at 10:01 am

#310007

Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

both ways are available via the extended proc xp_logininfo , but the limitation is the group/user has to be added to SQL first before you can enumerate them.

if you want it for users outside of SQL, you need a powershells script that does the same thing.

if i've created a login for either the windows gorup or the individual user, i can see the groups he belongs to, or the members within a group.

EXEC master..xp_logininfo @acctname = 'disney\lowell',@option = 'all' -- Show all paths a user gets his auth from

go

EXEC master..xp_logininfo @acctname = 'disney\Developers,@option = 'members' -- show group members

this snippet enumerates all users ithin all windows groups on a given SQL isntance:

IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL

DROP TABLE [dbo].[#TMP]

CREATE TABLE [dbo].[#TMP] (

[ACCOUNT NAME] NVARCHAR(256) NULL ,

[TYPE] VARCHAR(8) NULL ,

[PRIVILEGE] VARCHAR(8) NULL ,

[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

[PERMISSION PATH] NVARCHAR(256) NULL )

DECLARE @cmd VARCHAR(MAX);

SELECT @cmd = s.Colzs

FROM(SELECT

Colzs = STUFF((SELECT ';' + 'INSERT INTO #TMP EXEC master..xp_logininfo @acctname = ''' + name +''',@option = ''members'' '

FROM master.sys.server_principals

WHERE type_desc = 'WINDOWS_GROUP'

AND name NOT LIKE '%$%' --avoid errors like Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQL$MSSQLSERVER1', error code 0x8ac.

FOR XML PATH('')

),1,1,'')

) s

SET @cmd = REPLACE(@cmd,';',';' + CHAR(13) + CHAR(10))

print @cmd

exec(@cmd)

SELECT * FROM #tmp

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!

dwilliscp SSCrazy Eights Points: 9263 More actions · Answer 2

The domain group is set up as a login.. but when I run.. I get the following error:

Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42

Could not obtain information about Windows NT group/user 'Domain\LG-ANY', error code 0x8ac.

If I try... EXEC master..xp_logininfo @acctname = 'Domain\LG-ANY',@option = 'all' it does return one row with the info on this group.

dwilliscp SSCrazy Eights Points: 9263 More actions · Answer 3

I was looking at microsofts code.. and running EXEC master..xp_logininfo @acctname = NULL,@option = NULL

One thing I see is that permission path is NULL, shouldn't that have my domain group in it? If I am following their code ..

-- HANDLE 'members' QUERY --

if (@option = 'members')

begin

declare @priv varchar(8)

select @priv = case when sysadmin = 1 then 'admin' else 'user' end

from master..syslogins where isntname = 1 and loginname = @acctname and hasaccess = 1

if @priv is not null

select'account name' = domain+N'\'+name,

'type' = convert(varchar(8), case when sidtype = 1 then 'user' else 'group' end),

'privilege' = @priv,

'mapped login name' = domain+N'\'+name,

'permission path' = @acctname

from OpenRowset(NetGroupGetMembers, @acctname) order by 3, 1

else

select'account name' = convert(sysname, null),

'type' = convert(varchar(8), null),

'privilege' = @priv,

'mapped login name' = convert(sysname, null),

'permission path' = convert(sysname, null)

where 0=1-- empty result set

return @@error

end

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

Look at:

IS_MEMBER ()

as well.

I think you would need authority to impersonate the user to test their group membership(s).

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

dwilliscp SSCrazy Eights Points: 9263 More actions · Answer 5

I am working with our AD team to see what is going on... while the MSDN site does not say any thing, I think it is permission related. There was one group that I could pull data back on.. out of about 7 we tried. Interesting it was not "Domain Users" that worked.

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 6

These run from the command line.

This finds the users in an AD group:

net group /domain NAMEOFGROUP

This finds the groups an AD user is in:

net users THENAMEOFTHEUSER /domain