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 ( Login = @username
OR shell = @username
OR Email = @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
CREATE PROCEDURE ListMailUsers @esid integer AS
Select Login, Shell, EMail, d.MailDomain, Password, HomeDir