RE: [Emerald] FW: Mail Server Crash

David Routh ( drouth@davlin.net )
Sat, 13 Nov 1999 19:30:25 -0600

At 04:58 PM 11/13/1999 -0800, you wrote:
>On Sat, 13 Nov 1999, Kelly Wright wrote:
>
>> 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 :(
>

In other words, so I understand this right, running this proc will allow me
to just put the email address I want to use in the email field of the MBR
and not have to create a new billing group, etc. to allow that email
address to collect it's mail?

What happens if we have others like user@yahoo.com in the email address
space that we use to send invoices to? Does that cause problems.

Thanks, David

>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
>

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart