CREATE DATABASE [AspNetUserManagement]

CREATE TABLE [user](
	[pk] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT NEWID(),
	[id] [nvarchar](50) NOT NULL,
	[password] [nvarchar](100) NOT NULL,
	[passwordSalt] [nvarchar](100) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'AddUser'))
DROP PROCEDURE AddUser
GO
CREATE PROCEDURE AddUser
     @USER_ID                   NVARCHAR(20)
    ,@USER_PW                   NVARCHAR(100)
    ,@USER_SALT                   NVARCHAR(100)
    ,@USER_NM                   NVARCHAR(20)
----WITH ENCRYPTION
AS
BEGIN
    INSERT INTO  [user]
    (
        [id]
        ,[password]
        ,[passwordSalt]
        ,[name]
    )
    VALUES
    (
     @USER_ID
    ,@USER_PW
    ,@USER_SALT
    ,@USER_NM
    )
END
GO

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'GetUserByUserID'))
DROP PROCEDURE GetUserByUserID
GO
CREATE PROCEDURE GetUserByUserID
     @USER_ID                   NVARCHAR(50)
----WITH ENCRYPTION
AS
BEGIN
    SELECT [pk]
        ,[id]
        ,[password]
        ,[passwordSalt]
        ,[name]
    FROM [user]
    WHERE id = @USER_ID
END
GO

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'GetUserByUserPK'))
DROP PROCEDURE GetUserByUserPK
GO
CREATE PROCEDURE GetUserByUserPK
     @USER_PK                   NVARCHAR(50)
----WITH ENCRYPTION
AS
BEGIN
    SELECT [pk]
        ,[id]
        ,[password]
        ,[passwordSalt]
        ,[name]
    FROM [user]
    WHERE pk = @USER_PK
END
GO