goldauth/InitializeDatabase.sql at master · brainstain/goldauth · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
CREATE DATABASE GoldsteinAuth
GO
USE GoldsteinAuth;
GO
CREATE TABLE dbo.Users_
(
Id uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
UserName varchar(50),
Email varchar(100),
LastLogin datetime,
IsActive bit,
IsLocked bit,
IsReset bit,
LoginAttemptCount int,
PasswordHash varbinary(64),
Salt varbinary(64),
ResetToken varbinary(64) DEFAULT null
);
GO
CREATE UNIQUE INDEX IX_Users_UserName
	ON dbo.Users_ (UserName);
GO
CREATE UNIQUE INDEX IX_Users_Email
	ON dbo.Users_ (Email);
GO
CREATE PROCEDURE dbo.AddUser
	@UserName varchar(50),
	@Email varchar(100),
	@LastLogin datetime,
	@IsActive bit,
	@IsLocked bit,
	@LoginAttemptCount int,
	@PasswordHash varbinary(64),
	@Salt varbinary(64)
AS
BEGIN
	INSERT INTO [GoldsteinAuth].[dbo].[Users_]
	(Id, UserName, Email, LastLogin, IsActive, IsLocked, LoginAttemptCount, PasswordHash, Salt)
	VALUES(DEFAULT, @UserName, @Email, @LastLogin, @IsActive, @IsLocked, @LoginAttemptCount, @PasswordHash, @Salt)
END
GO
CREATE PROCEDURE dbo.GetUserByUserName
	@SearchUserName varchar(50),
	@UserName varchar(50) OUTPUT,
	@Email varchar(100) OUTPUT,
	@LastLogin datetime OUTPUT,
	@IsActive bit OUTPUT,
	@IsLocked bit OUTPUT,
	@LoginAttemptCount int OUTPUT,
	@PasswordHash varbinary(64) OUTPUT,
	@Salt varbinary(64) OUTPUT
AS
BEGIN
	SELECT
		@UserName = UserName,
		@Email = Email,
		@LastLogin = LastLogin,
		@IsActive = IsActive,
		@IsLocked = IsLocked,
		@LoginAttemptCount = LoginAttemptCount,
		@PasswordHash = PasswordHash,
		@Salt = Salt
	FROM
		Users_ u
	WHERE
		u.UserName = @SearchUserName
END
GO
CREATE PROCEDURE dbo.UpdateUserByUserName
	@UserName varchar(50) OUTPUT,
	@Email varchar(100) OUTPUT,
	@LastLogin datetime OUTPUT,
	@IsActive bit OUTPUT,
	@IsLocked bit OUTPUT,
	@LoginAttemptCount int OUTPUT,
	@PasswordHash varbinary(64) OUTPUT,
	@Salt varbinary(64) OUTPUT
AS
BEGIN
	UPDATE Users_
	SET
		Email = @Email,
		LastLogin = @LastLogin,
		IsActive = @IsActive,
		IsLocked = @IsLocked,
		LoginAttemptCount = @LoginAttemptCount,
		PasswordHash = @PasswordHash,
		Salt = @Salt
	WHERE
		UserName = @UserName
END
GO