Re: FIX: VerifyMailUser and Multiple Virtual Domains

Thomas Massano ( ThomasM@InletCorp.com )
Sat, 22 Aug 1998 10:26:56 -0400

Rudy, looks like a great idea! I'm gonna give it a try, thanks!

At 06:45 PM 8/20/98 , you wrote:
>After spending some time battling with SQL's stored Procedure VerifyMailUser
>to get it to list only 1 user for the proper domain, I had modified the
>following lines:
>
>AND ( Login = @username
>OR shell = @username
>OR Email = @username + "@" + @domain)
>
>To look like this:
>
>AND ( Email = @username + "@" + @domain
>OR shell + "@" + d.maildomain = @username + "@" + @domain
>OR Login + "@" + d.maildomain = @username + "@" + @domain)
>
>In Emerald, when I set someone a new subaccount for the isp domain, I use the
>following:
>Username: support
>Password: xxxx
>EMail: <Blank>
>
>Now when I have a client who has a virtual domain name on our server (eg:
>test.com), I use the following to setup his Email information to avoid
>username and email login duplication:
>
>Username: support-test.com
>Password: yyyy
>EMail: support@test.com
>
>Now when the user tries loggin in via the virtually hosted domain (eg: pop3:
>mail.test.com) when it asks for username, he would type in support and his
>yyyy password and VerifyMailUser will properly select the right username for
>the account. The support-test.com username is there as reference to know
>which support account from the domain name it belongs to.
>
>Wel here is the FULL VerifyUserName procedure if anyone would like to use it.
>
>PLEASE NOTE: If you did not specify Domains and Billing Groups for
Domains in
>the Emerald Administration, You MUST Add the EMail: Address for each domain
>client. Without it, he will not be able to login.
>
>Hope this helps a lot of you Mail domain hosters.
>
>----
>Rudy Komsic
>President, Network Administrator - Cyberglobe Communications Inc.
>4996 Place de la Savane, Suite 200, Montreal, Quebec, H4P 1Z6
>Tel: (514)342-3883 Fax: (514)342-5139 E-Mail: rudyk@cyberglobe.net
>
>VerifyMailUser Procedure below.
>-----
>if exists (select * from sysobjects where id =
object_id('dbo.VerifyMailUser')
>and sysstat & 0xf = 4)
> drop procedure dbo.VerifyMailUser
>GO
>
>CREATE PROCEDURE VerifyMailUser @username varchar(32), @domain varchar(32),
>@esid integer AS
>
>Select Login, Shell, EMail, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, AccountTypes at
>Where ma.CustomerID = sa.CustomerID
>AND ma.GroupID = g.GroupID
>AND g.DomainID = d.DomainID
>AND at.AccountType = sa.AccountType
>AND at.ExternalSystemID = @esid
>AND ( Email = @username + "@" + @domain
>OR shell + "@" + d.maildomain = @username + "@" + @domain
>OR Login + "@" + d.maildomain = @username + "@" + @domain)
>
>UNION
>
>Select Login, Shell, EMail=a.Alias, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, Aliases a,
>AccountTypes at
>Where ma.CustomerID = sa.CustomerID
>AND ma.GroupID = g.GroupID
>AND g.DomainID = d.DomainID
>AND sa.AccountID = a.AccountID
>AND at.AccountType = sa.AccountType
>AND at.ExternalSystemID = @esid
>AND a.Alias = @username + "@" + @domain
>GO
>