Post reply

How to query AD to return IF ORIGINAL_LOGIN() is in a specific AD group

  • June 13, 2012 at 4:48 am

    #257590

    Hi all,

    I am trying to write a query which ultimately will be coupled to a login trigger. The code below does do what i want (though for test purposes).

    Thing is the team leader wants it as a single query (no cursor, no beat around the bush), basically he wants:

    SELECT NAME FROM LDAP WHERE NAME = ORIGINAL_LOGIN() AND MEMBEROF 'GROUP'

    I dont believe this can be acheived with LDAP in a single query but I hope to be proven wrong.

    DECLARE @accessgroup VARCHAR(50)

    SET @accessgroup = 'SQLDBAs'

    DECLARE @user VARCHAR(50)

    SET @user = UPPER(ORIGINAL_LOGIN())

    CREATE TABLE #MemberOfGroups(

    Group_Name varchar(400),

    Common_Name varchar(400),

    Display_Name varchar(400),

    AD_Login VARCHAR(400))

    SET NOCOUNT ON

    DECLARE @t VARCHAR(100),

    @t2 VARCHAR(1000),

    @ot VARCHAR (4000),

    @tt VARCHAR (4000);

    DECLARE Group_Cursor CURSOR

    FOR

    SELECT cn, distinguishedName

    FROM OPENQUERY

    (DOMAINCONTROLLER,'SELECT cn, distinguishedName

    FROM ''LDAP://DOMAINCONTROLLER''

    WHERE objectCategory = ''group'' and CN = ''SQLDBAs'' ')

    OPEN Group_Cursor

    FETCH NEXT FROM Group_Cursor INTO @t, @t2

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @ot = '''SELECT cn, displayName, sAMAccountName

    FROM ''''LDAP://DOMAINCONTROLLER''''

    WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''

    AND memberOf=''''' + @t2 + '''''';

    SET @tt = 'select '+ ''''+@t+'''' +' As Group_Name, cn, displayName, ''COMPANY\'' + sAMAccountName from openquery(DOMAINCONTROLLER,'+ @ot +''') order by cn'

    INSERT INTO #MemberOfGroups(Group_Name, Common_Name, Display_Name, AD_Login)

    EXEC (@tt)

    FETCH NEXT FROM Group_Cursor INTO @t, @t2

    END

    CLOSE Group_Cursor

    DEALLOCATE Group_Cursor

    IF EXISTS (SELECT * FROM #MemberOfGroups WHERE AD_Login = @user)

    BEGIN

    --order by displayName

    DROP TABLE #MemberOfGroups

    PRINT 'User is a member of ' + @accessgroup + ' ! ! !'

    END

    ELSE

    BEGIN

    DROP TABLE #MemberOfGroups

    PRINT 'Member not found ! ! !'

    END

    Code originally came from a seperate source and was modifyed for purpose. I dont have the URL anymore so cant give credit. 🙁 sorry

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 13, 2012 at 5:24 am

    #1500269

    IF EXISTS(

    SELECT 'mydomain\' + sAMAccountName

    FROM OPENQUERY

    (ENTMASTER1,'SELECT cn, displayName, sAMAccountName

    FROM ''LDAP://mydomaincontroller''

    WHERE objectCategory = ''Person'' AND objectClass = ''user''

    AND memberOf=''CN=myadgroup,CN=Users,DC=xx,DC=xxxxx,DC=xx,DC=xx''')

    WHERE sAMAccountName = parsename(REPLACE(ORIGINAL_LOGIN(),'\','.'),1))

    RAISERROR('ASDFAS', 0, 1) with nowait

    else

    RAISERROR('999999', 0, 1) with nowait

  • June 13, 2012 at 5:32 am

    #1500274

    another possibility, but where you would have to grant execute permissions to Everyone ON xp_logininfo (or the trigger has to Execute AS a power user's context) so they can get the groups the login belongs to:

    CREATE TABLE [dbo].[#TMP] (

    [ACCOUNT NAME] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [TYPE] VARCHAR(8) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [PRIVILEGE] VARCHAR(8) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [MAPPED LOGIN NAME] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [PERMISSION PATH] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS)

    INSERT INTO #tmp

    EXEC master..xp_logininfo @acctname = ORIGINAL_LOGIN() ,@option = 'all'

    --EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all'

    --change to 'YourDomain\SQLDBAs' ?

    IF EXISTS(SELECT 1 FROM [dbo].[#TMP] WHERE [account name] = ORIGINAL_LOGIN() AND [permission path] = 'BUILTIN\Administrators')

    PRINT 'whoopee!'

    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!

  • June 13, 2012 at 5:36 am

    #1500276

    All,

    Thanks for the speedy responses. Lowell, that quite a good idea but I think Andrew's query is more what I'm looking for, needing no additional rights or tables.

    Thanks again.

    A.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 13, 2012 at 8:18 am

    #1500398

    Hey All,

    I have moved along with this and now have the below which i have been testing on my ss2k5 local instance.

    /****** Object: DdlTrigger [Logon_Trigger_Monitor_Excel] Script Date: 06/13/2012 10:07:47 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Logon_Trigger_Monitor_Excel]

    ON ALL SERVER WITH EXECUTE AS 'COMPANY\SQLSVC'

    FOR LOGON

    AS

    BEGIN

    IF APP_NAME() LIKE '%MICROSOFT OFFICE%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%'

    IF UPPER(ORIGINAL_LOGIN()) LIKE 'COMPANY\%'

    BEGIN

    IF (SELECT 'COMPANY\' + sAMAccountName FROM OPENQUERY

    (ENTMASTER1,'SELECT cn, displayName, sAMAccountNameFROM ''LDAP://DOMQAINCONTROLLER''

    WHERE objectCategory = ''Person'' AND objectClass = ''user''

    AND memberOf=''CN=sqldbas,CN=Users,DC=int,DC=company,DC=co,DC=uk''')

    WHERE sAMAccountName = parsename(REPLACE(ORIGINAL_LOGIN(),'\','.'),1))

    = 0

    ROLLBACK

    END

    END

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [Logon_Trigger_Monitor_Excel] ON ALL SERVER

    GO

    The problem is the main query isnt being run and is throwing up 'an error occured while preparing the query {query text} for execution against ole db provider adsdsoobject for linked server {LS}' (viewed via profiler).

    The end result is the trigger is not allowing logon via excel because the trigger query cant complete.

    Any ideas?

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 13, 2012 at 8:31 am

    #1500406

    Lowell (6/13/2012)

    another possibility, but where you would have to grant execute permissions to Everyone ON xp_logininfo (or the trigger has to Execute AS a power user's context) so they can get the groups the login belongs to:

    CREATE TABLE [dbo].[#TMP] (

    [ACCOUNT NAME] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [TYPE] VARCHAR(8) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [PRIVILEGE] VARCHAR(8) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [MAPPED LOGIN NAME] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS,

    [PERMISSION PATH] NVARCHAR(256) NULL COLLATE SQL_Latin1_General_CP1_CI_AS)

    INSERT INTO #tmp

    EXEC master..xp_logininfo @acctname = ORIGINAL_LOGIN() ,@option = 'all'

    --EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all'

    --change to 'YourDomain\SQLDBAs' ?

    IF EXISTS(SELECT 1 FROM [dbo].[#TMP] WHERE [account name] = ORIGINAL_LOGIN() AND [permission path] = 'BUILTIN\Administrators')

    PRINT 'whoopee!'

    Lowell,

    I just tried this out and cant see where the check to AD is performed.

    Sorry if im being dense.

    Adam.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 13, 2012 at 9:45 am

    #1500473

    I'm not familiar with the inner workings of the extended proc xp_logininfo, but it does query the domain controller for the groups a user belongs to, or the users that belong to a group:

    the problem with using that in a trigger, of course, is going to be permissions to run the xp_logininfo proc.

    EXEC master..xp_logininfo

    @acctname = 'disney\lizaguirre',

    @option = 'all' -- Show all paths a user gets his auth from

    go

    EXEC master..xp_logininfo

    @acctname = 'disney\authenticatedusers',

    @option = 'members' -- show group members

    here's the same code i ahd before, wrapped up as a single select statemetn as you requested before.

    to do that, you need to allow both openquery and resolve the permissions thing if you are doing it in a login trigger.

    another option would be to store the data in a table, ow often do new users or groups really get added?

    then you could query a table directly,a nd have some job update the table once a day or something.

    --all inline as a single command:

    IF EXISTS(SELECT 1 FROM (SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=DEV223\SQL2005;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC master..xp_logininfo @acctname = ''disney\lizaguirre'',@option = ''all'' '

    )

    ) MyAlias

    WHERE [account name] = ORIGINAL_LOGIN()

    AND [permission path] = 'BUILTIN\Administrators')

    PRINT 'whoopee!'

    DROP TABLE #HoldDate

    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!

  • June 14, 2012 at 9:33 am

    #1501110

    Thanks Lowell.

    For the interest of anybody, we have decided to go with the below: Origenal code pilfered from here.

    /****** Object: DdlTrigger [Logon_Trigger_Monitor_Excel] Script Date: 06/13/2012 16:32:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Logon_Trigger_Monitor_Excel]

    ON ALL SERVER WITH EXECUTE AS 'HERMES\SERVICEACT'

    FOR LOGON

    AS

    BEGIN

    BEGIN

    IF APP_NAME() LIKE '%MICROSOFT OFFICE%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%'

    IF UPPER(ORIGINAL_LOGIN()) LIKE 'COMPANY\%'

    BEGIN

    -- declare temp table to hold results

    DECLARE @WindowsGroupMembers TABLE(AccountName VARCHAR(255),

    TYPE VARCHAR(50),privilege VARCHAR(50),Mapped_Login_Name VARCHAR(255),permission_path VARCHAR(255));

    --declare var for groupname and connection user

    DECLARE @GroupName VARCHAR(255)

    SET @GroupName = 'HERMES\ADGROUP'

    DECLARE @user VARCHAR(50)

    SET @user = ORIGINAL_LOGIN()

    -- populate table with results of xp_login (members)

    INSERT INTO @WindowsGroupMembers([AccountName],[TYPE],[privilege],[Mapped_Login_Name],[permission_path])

    EXEC xp_logininfo @GroupName, 'members'

    IF (SELECT COUNT(*) FROM @WindowsGroupMembers WHERE AccountName = @user) = 0

    ROLLBACK

    END

    END

    END

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [Logon_Trigger_Monitor_Excel] ON ALL SERVER

    GO

    Adam Zacks-------------------------------------------Be Nice, Or Leave

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy