June 13, 2012 at 4:48 am
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
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
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
June 13, 2012 at 5:36 am
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
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
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
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
June 14, 2012 at 9:33 am
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