How to create Logins, Users, Schemas and Database Roles

CREATE LOGIN [Hello.org] WITH PASSWORD=N' Ñèw?qÉSyDÿ8?s ! Mån¯;Ì®3¡y¹ ? ', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

ALTER LOGIN [Hello.org] DISABLE
GO


-------------------------------------------------------------

CREATE LOGIN [Hello.org_Shop] WITH PASSWORD=N'B2+ó ð?;aMÈb ÿ±il®¡ÇùÍ áLçÒ?A: ¡', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

ALTER LOGIN [Hello.org_Shop] DISABLE
GO

---------------------------------------------------------------------------



CREATE USER [Hello.org] FOR LOGIN [Hello.org] WITH DEFAULT_SCHEMA=[Hello.org_SP]
GO
-------------------------------------------------------------------------

CREATE USER [Hello.org_Shop] FOR LOGIN [Hello.org_Shop] WITH DEFAULT_SCHEMA=[Hello.org_ShopSP]
GO


-------------------------------------------------------------------------------------------------------------------------

CREATE SCHEMA [Hello.org_SP] AUTHORIZATION [dbo]
GO

CREATE SCHEMA [Hello.org_ShopSP] AUTHORIZATION [dbo]
GO


CREATE SCHEMA [Hello.org_InternalSP] AUTHORIZATION [dbo]
GO


---------------------------------------------------------------------------------------------------------------

CREATE ROLE [DBR_Hello.org] AUTHORIZATION [dbo]
GO


CREATE ROLE [DBR_Hello.org_Shop] AUTHORIZATION [dbo]
GO

Authenticate Member SP

ALTER PROCEDURE [The].[prc_MemberAuthenticate]
@chvEmail varchar(128),
@chvnPassword nvarchar(100),
@ReturnCode int OUT
AS
DECLARE @chvnMemberPassword NVARCHAR(128),
@chvMemberNo varchar (9),
@chvEmailDB varchar(128),
@IsPaid bit


SET @chvMemberNo = ''
SET @IsPaid = 0

BEGIN
SELECT @IsPaid = IsPaid,@chvMemberNo = MemberID, @chvnMemberPassword = [Password], @chvEmailDB = Email
FROM The.Memberships WHERE Email = @chvEmail and IsPaid = 1

IF (@IsPaid = 0)
BEGIN
SET @returnCode = -3 --Unpaid
END
ELSE IF (@chvEmailDB is null OR @chvEmailDB = '')
BEGIN
SET @returnCode = -1 --Wrong UserName
END
ELSE IF (@chvnPassword <> @chvnMemberPassword)
BEGIN
SET @returnCode = -2 --Wrong Password
END
--ELSE IF (@var_Status=0)
-- BEGIN
-- SET @returnCode = -3 -- Not active
-- END
ELSE
BEGIN
SET @returnCode = 1 -- Login successfully
END

IF (@returnCode = 1)
BEGIN
SELECT MemberID,MemberNo, (FirstName + ' ' + LastName) as Name
FROM The.Memberships
WHERE Email = @chvEmail
END
END