July 17, 2014 at 10:01 am
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?
July 17, 2014 at 10:18 am
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
July 18, 2014 at 8:03 am
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.
July 18, 2014 at 8:17 am
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
July 18, 2014 at 11:41 am
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!
July 21, 2014 at 10:10 am
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.
July 21, 2014 at 12:13 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply