Monday, July 14, 2014

Create Aspnet Membership User With Password using SQL


CREATE FUNCTION [dbo].[base64_encode] (@data VARBINARY(max)) RETURNS VARCHAR(max)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
 RETURN (SELECT [text()] = @data FOR XML PATH(''))
END


CREATE FUNCTION [dbo].[base64_decode] (@base64_text VARCHAR(max)) RETURNS VARBINARY(max)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @x XML; SET @x = @base64_text
RETURN @x.value('(/)[1]', 'VARBINARY(max)')
END


CREATE PROCEDURE [dbo].[p_Create_ASP_User]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@ClearTextPassword nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@UserId uniqueidentifier OUTPUT
AS
BEGIN
 DECLARE @CurrentTimeUtc datetime
 DECLARE @CreateDate datetime
 DECLARE @PasswordFormat int
 DECLARE @UniqueEmail int
 DECLARE @IsApproved bit
 DECLARE @salt uniqueidentifier=newid()
 DECLARE @encoded_hashed_password nvarchar(128)
 DECLARE @encoded_salt nvarchar(128)
 DECLARE @RC int
 --SET CONSTANTS

 SET @IsApproved = 1
 SET @UniqueEmail = 0
 SET @PasswordFormat = 1

 --SET VARIABLES
 SET @salt = NEWID()
 SET @encoded_salt = dbo.base64_encode(@salt)
 SET @UserId = NEWID()
 SET @CurrentTimeUtc = GetDate()
 SET @CreateDate = GetDate()

 --SET EMPLOYER INFORMATION
 SET @encoded_hashed_password = dbo.base64_encode(HASHBYTES('SHA1', Cast(@salt as varbinary(MAX)) + CAST(@ClearTextPassword AS varbinary(MAX)) ))

 EXECUTE @RC = aspnet_Membership_CreateUser
 @ApplicationName
 ,@UserName
 ,@encoded_hashed_password
 ,@encoded_salt
 ,@Email
 ,@PasswordQuestion
 ,@PasswordAnswer
 ,@IsApproved
 ,@CurrentTimeUtc
 ,@CreateDate
 ,@UniqueEmail
 ,@PasswordFormat,@UserId OUTPUT

END

No comments:

Post a Comment