Thanks, Darren,
It works fine with AD, further more, I tested it in a machine without AD, it failed, so I changed AddUser stored procedure as following, it works fine for window authentication without active directory.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddUser]
@PortalID int,
@Username nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@AffiliateId int,
@IsSuperUser bit,
@Email nvarchar(256),
@DisplayName nvarchar(100),
@UpdatePassword bit,
@Authorised bit
AS
DECLARE @UserID int
SELECT @UserID = UserID
FROM Users
WHERE Username = @Username
-- begin adding firstname, lastname, displayname if null
IF ( @FirstName is null OR @FirstName = '' )
BEGIN
SET @FirstName = @Username
END
IF ( @LastName is null OR @LastName = '' )
BEGIN
SET @LastName = @Username
END
IF ( @DisplayName is null OR @DisplayName = '' )
BEGIN
SET @DisplayName = @Username
END
-- end of adding
IF @UserID is null
BEGIN
INSERT INTO Users (
Username,
FirstName,
LastName,
AffiliateId,
IsSuperUser,
Email,
DisplayName,
UpdatePassword
)
VALUES (
@Username,
@FirstName,
@LastName,
@AffiliateId,
@IsSuperUser,
@Email,
@DisplayName,
@UpdatePassword
)
SELECT @UserID = SCOPE_IDENTITY()
END
IF @IsSuperUser = 0
BEGIN
IF not exists ( SELECT 1 FROM UserPortals WHERE UserID = @UserID AND PortalID = @PortalID )
BEGIN
INSERT INTO UserPortals (
UserID,
PortalID,
Authorised
)
VALUES (
@UserID,
@PortalID,
@Authorised
)
END
END
SELECT @UserID