Stored Procedure to add a user

— =============================================
— Description: Get User Identifier
— =============================================

CREATE PROCEDURE [dbo].[GetUserIdentifier]

— Add the parameters for the stored procedure here
@TheLogin NVARCHAR(255),
@ThePassword NVARCHAR(32),
@Identifier UNIQUEIDENTIFIER output,
@ResponseMessage NVARCHAR(128) output

AS
BEGIN
declare @MyUserID int;
declare @MyIdentifier UNIQUEIDENTIFIER;

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Remove leading and trailing spaces
— make Null if the remainder is an empty string
declare @MyLogin NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@TheLogin)), ”)
declare @MyPassword NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@ThePassword)), ”)

BEGIN TRY
if (@MyLogin is null) or (@MyPassword is null)
begin — Login and Password are blank
SET @ResponseMessage=’Login or Password can not be blank’;
end — Login and Password are blank
else
begin — Login and Password are not blank
— see if the logon already existed
SET @MyUserID=(SELECT ID from dbo.[User] where Login=LOWER(@MyLogin))
If @MyUserID is not NULL
begin — User existed
SET @MyIdentifier = (Select Identifier FROM [dbo].[User] WHERE Login=LOWER(@MyLogin) AND PasswordHash=HASHBYTES(‘SHA2_512′, @MyPassword+CAST(Salt AS NVARCHAR(36))))
IF @MyIdentifier IS NULL
begin
SET @ResponseMessage=’Incorrect password’;
end
else
begin
SET @ResponseMessage=’User successfully logged in’;
end
SET @Identifier = @MyIdentifier;
end — User existed
else
begin — User does not exist
SET @ResponseMessage=’Invalid login’;
SET @Identifier = @MyIdentifier;
end — User does not exist
end — Login and Password are not blank
END TRY

BEGIN CATCH
SET @ResponseMessage=ERROR_MESSAGE();
SET @Identifier = null;
END CATCH
END

GO
— =============================================
— Description: Add a User
— =============================================
CREATE PROCEDURE [dbo].[AddUser]
    — Add the parameters for the stored procedure here
    @NewLogin NVARCHAR(255),
    @NewPassword NVARCHAR(32),
    @Identifier UNIQUEIDENTIFIER output,
    @ResponseMessage NVARCHAR(128) output
AS
BEGIN
    declare @MyUserID int;
    declare @MySalt UNIQUEIDENTIFIER = NEWID();
    — Remove leading and trailing spaces
    — make Null if the remainder is an empty string
    declare @MyLogin NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@NewLogin)), ”)
    declare @MyPassword NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@NewPassword)), ”)
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRY
    if (@MyLogin is null) or (@MyPassword is null)
        begin — Login and Password are  blank
            SET @ResponseMessage=’Login and password can not be blank’;
            SET @Identifier = null;
        end — Login and Password are  blank
    else
        begin — Login and Password are not blank
            — see if the logon already existed
            SET @MyUserID=(SELECT ID from dbo.[User] where Login=LOWER(@MyLogin))
            If @MyUserID is NULL
                begin — New Login
                    SET @Identifier = NEWID();
                    INSERT INTO dbo.[User] ([Login], [Identifier], [PasswordHash], [Salt], [Registered], [Active])
                        VALUES(LOWER(@MyLogin), @Identifier, HASHBYTES(‘SHA2_512′, @MyPassword+CAST(@MySalt AS NVARCHAR(36))), @MySalt, 0, 0 );
                    SET @ResponseMessage=’Success’;
                end — New Login
            else
                begin — User existed
                    execute GetUserIdentifier
                    @TheLogin = @Mylogin,
                    @ThePassword = @MyPassword,
                    @Identifier = @Identifier OUTPUT,
                    @ResponseMessage = @ResponseMessage OUTPUT
                end — User existed
        end — Login and Password are not blank
    END TRY
    BEGIN CATCH
        SET @ResponseMessage=ERROR_MESSAGE();
        SET @Identifier = null;
    END CATCH
END

GO

Advertisements

2 thoughts on “Stored Procedure to add a user

  1. what is “GetUserIdentifier”? i get the error when running this script

    The module ‘AddUser’ depends on the missing object ‘GetUserIdentifier’. The module will still be created; however, it cannot run successfully until the object exists.

    1. Hello,
      Thanks for this, I just added the stored procedure:

      — =============================================
      — Description: Get the User Identifier
      — =============================================
      CREATE PROCEDURE [dbo].[GetUserIdentifier]
      — Add the parameters for the stored procedure here
      @TheLogin NVARCHAR(255),
      @ThePassword NVARCHAR(32),
      @Identifier UNIQUEIDENTIFIER output,
      @ResponseMessage NVARCHAR(128) output

      AS
      BEGIN
      declare @MyUserID int;
      declare @MyIdentifier UNIQUEIDENTIFIER;

      — SET NOCOUNT ON added to prevent extra result sets from
      — interfering with SELECT statements.
      SET NOCOUNT ON;

      — Remove leading and trailing spaces
      — make Null if the remainder is an empty string
      declare @MyLogin NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@TheLogin)), ”)
      declare @MyPassword NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@ThePassword)), ”)

      BEGIN TRY
      if (@MyLogin is null) or (@MyPassword is null)
      begin — Login and Password are blank
      SET @ResponseMessage=’Login or Password can not be blank’;
      end — Login and Password are blank
      else
      begin — Login and Password are not blank
      — see if the logon already existed
      SET @MyUserID=(SELECT ID from dbo.[User] where Login=LOWER(@MyLogin))

      If @MyUserID is not NULL
      begin — User existed
      SET @MyIdentifier = (Select Identifier FROM [dbo].[User] WHERE Login=LOWER(@MyLogin) AND PasswordHash=HASHBYTES(‘SHA2_512′, @MyPassword+CAST(Salt AS NVARCHAR(36))))

      IF @MyIdentifier IS NULL
      begin
      SET @ResponseMessage=’Incorrect password’;
      end
      else
      begin
      SET @ResponseMessage=’User successfully logged in’;
      end
      SET @Identifier = @MyIdentifier;
      end — User existed
      else
      begin — User does not exist
      SET @ResponseMessage=’Invalid login’;
      SET @Identifier = @MyIdentifier;
      end — User does not exist
      end — Login and Password are not blank
      END TRY
      BEGIN CATCH
      SET @ResponseMessage=ERROR_MESSAGE();
      SET @Identifier = null;
      END CATCH

      END

      GO

      Thanks for pointing this out.

      Hans

Post a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s