Hi
I've created a stored procedure (see below) which accesses the Active
Directory and SQL server to get "real names" back. When I run the
stored procedure in Query Analyzer it returns the expected results,
however when I try to create a Web Assistant job based upon the
procedure I get the SQL-DMO message:
Error 7410 Remote Access not allowed for Windows NT Useractivated by
SETUSER.
The procedure is being run (and the job created) as the account which
owns the SQL Server installation, and this account has AD admin
permissions.
Any suggestions?
CREATE VIEW dbo.vw_account_ad
AS
SELECT a.Name AS ad_name, dbo.Accounts.*
FROM dbo.Accounts INNER JOIN
OPENQUERY(ADSI,
'select SamAccountName, Name FROM
''LDAP://w2k-bspad1/ ou=users,ou=bsp,DC=ad,DC=bl,DC=uk'' where
objectcategory=''person'' ') a ON
SUBSTRING(dbo.Accounts.Account_Name,
CHARINDEX('\', dbo.Accounts.Account_Name) + 1,
LEN(dbo.Accounts.Account_Name) - CHARINDEX('\',
dbo.Accounts.Account_Name)) = a.SamAccountName
CREATE PROCEDURE [dbo].[usp_event_report] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT Code_Name, Account_Name + ' ('+ad_Name+')' as 'Account Name',
Date_Occured, Result
FROM Usage_Codes, Usage, vw_account_ad
WHERE Usage.Code_ID = Usage_Codes.Code_ID
AND Usage.Account_ID = vw_account_ad.Account_ID
AND datepart(month,Date_Occured) = datepart(month,getdate())
ORDER BY Code_Name, Account_Name, Date_Occured
GO
Chloe Crowder
The British Library