> Our main domain "buz.net" is working with Emerald. We have about 100 more
> domains we need to add so that the users in Emerald will be able to POP
> their mail with the appropriate domain name. Not sure what we have to do.
> I did a test on a seperate domain name. It bounces all mail back because it
> can't find the user in the db. Does the script below fix that issue?
This proc allows user@domain in the Email field to work correctly with
mailsite. The procedure Dale posted does everything but this one on the
account of a bug in the query :(
ciao,
Peter
CREATE PROCEDURE VerifyMailUser @username varchar(32), @domain
varchar(32), @esid integer AS
CREATE TABLE #AccountIDs (AccountID INT, DomainMatch BIT)
INSERT INTO #AccountIDs
SELECT AccountID,0 FROM SubAccounts sa WHERE Login = @username
UNION
SELECT AccountID,0 FROM SubAccounts WHERE Email = @username
UNION
SELECT AccountID,0 FROM SubAccounts WHERE Shell = @username
UNION
SELECT AccountID,1 FROM SubAccounts WHERE Email = @username + '@' + @domain
DELETE ai
FROM #AccountIDs ai, SubAccounts sa, MasterAccounts ma
WHERE ai.AccountID = sa.AccountID
AND sa.CustomerID = ma.CustomeriD
AND (sa.Active <> 1 OR ma.Active <> 1)
IF (SELECT COUNT(AccountID) FROM #AccountIDs) = 0
INSERT INTO #AccountIDs
SELECT AccountID,1 FROM Aliases WHERE Alias = @username + '@' + @domain
Select Login, Shell, Email,@domain AS MailDomain,
CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit > ma.Balance) OR
DateAdd(Day, (ma.Extension+ma.OverDue+1), maExpireDate) < GetDate()
THEN RIGHT(CONVERT(VARCHAR(10),RAND()),4) + RIGHT(CONVERT(VARCHAR(10),RAND()),4)
ELSE sa.Password END AS Password, HomeDir
From MasterAccounts ma, SubAccounts sa, AccountTypes at1, Groups g, #AccountIDs ai
WHERE ma.CustomerID = sa.CustomerID
AND sa.AccountID = ai.AccountID
AND ma.GroupID = g.GroupID
AND (ai.DomainMatch = 1 OR g.DomainID
IN (SELECT d.DomainID FROM Domains d WHERE d.MailDomain = @domain))
AND at1.AccountType = sa.AccountType
AND at1.ExternalSystemID = @esid
DROP TABLE #AccountIDs
GO
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart