June 14, 2011 at 1:25 pm
I know this is not an aspnet site, but MSSQL is used by asp.net so I guess some of you know both worlds
I use the aspnetdb for my membership for my site.
At the moment I have this logic
1 New Member joins - aspnetdb creates a new record at server
2) via the client (ie the site) - I get the new GUID and post details at a custom membership table and aspnet profile. This custom table has a userfriendly Identity ID not a GUID. You have to update the aspnetdb profile via the client.
This proces requires client to server back to client then back to server (yuk)
This code below creates the new member in the aspnetdb membership table (ie dbo.aspnet_Membership). This table has a GUID identity, and is created when the new member is added (ie dbo.aspnet_Membership.UserID ).
What I want to do on a new member being INSTERTed is GET the latest GUID (dbo.aspnet_Membership.UserID )and POST this to my custom membership table.
In dbo.aspnet_Membership has a 'dbo.aspnet_Membership.CreateDate' field. So I know I can do this immediatly after the INSERT below
SELECT UserID FROM dbo.aspnet_Membership
WHERE CreateDate = MAX(CreateDate)
Then insert the above into my custom membership table, all within the the ' BEGIN TRANSACTION' in the below code.
QUESTION: Is this secure, will it happen every time, should I LOCK the dbo.aspnet_Membership table while the process is doing this, I ask this because what IF I get many members signing up at the same time. Is there another was to get the latest GUID (ie dbo.aspnet_Membership.UserID )
ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
-- CUSTOM ADDON CODE STARTS
IF( @@ERROR = 0 )
BEGIN
INSERT INTO dbo.DIM_User_Approved ([UserGUID],[Subs_Code], [Subs_End])
SELECT UserID, 5, DATEADD(day, 5, GETUTCDATE()) FROM dbo.aspnet_Membership
WHERE CreateDate = (SELECT MAX(CreateDate) FROM dbo.aspnet_Membership)
END
-- CUSTOM ADDON CODE ENDS
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
I have added this code..in the above at the error checks
-- CUSTOM ADDON CODE STARTS
IF( @@ERROR = 0 )
BEGIN
INSERT INTO dbo.DIM_User_Approved ([UserGUID],[Subs_Code], [Subs_End])
SELECT UserID, 5, DATEADD(day, 5, GETUTCDATE()) FROM dbo.aspnet_Membership
WHERE CreateDate = (SELECT MAX(CreateDate) FROM dbo.aspnet_Membership)
END
-- CUSTOM ADDON CODE ENDS
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply