RE: [Emerald] FW: Mail Server Crash

David Routh ( )
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 "" 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 in the email address
space that we use to send invoices to? Does that cause problems.

Thanks, David

>CREATE PROCEDURE VerifyMailUser @username varchar(32), @domain
>varchar(32), @esid integer AS
>CREATE TABLE #AccountIDs (AccountID INT, DomainMatch BIT)
>SELECT AccountID,0 FROM SubAccounts sa WHERE Login = @username
>SELECT AccountID,0 FROM SubAccounts WHERE Email = @username
>SELECT AccountID,0 FROM SubAccounts WHERE Shell = @username
>SELECT AccountID,1 FROM SubAccounts WHERE Email = @username + '@' + @domain
>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
>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()
>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
>For more information about this list (including removal) go to:

For more information about this list (including removal) go to: