Checking user's AD Group

Question

Checking user's AD Group

dwilliscp

SSCrazy Eights

Points: 9270
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: 323506 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: 9270 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: 9270 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: 100951 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) A socialist is someone who will give you the shirt off *someone else's* back.

dwilliscp SSCrazy Eights Points: 9270 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