Skip to content

Instantly share code, notes, and snippets.

@kenstone

kenstone/aspnet45 auth table defintions.sql

Created August 21, 2012 21:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kenstone/3419405 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kenstone/3419405 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. David Iffland created this gist Aug 21, 2012.
    44 changes: 44 additions & 0 deletions aspnet45 auth table defintions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,44 @@
    CREATE TABLE [dbo].[UserProfile] (
    [UserId] INT IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
    );

    CREATE TABLE [dbo].[webpages_Membership] (
    [UserId] INT NOT NULL,
    [CreateDate] DATETIME NULL,
    [ConfirmationToken] NVARCHAR (128) NULL,
    [IsConfirmed] BIT DEFAULT ((0)) NULL,
    [LastPasswordFailureDate] DATETIME NULL,
    [PasswordFailuresSinceLastSuccess] INT DEFAULT ((0)) NOT NULL,
    [Password] NVARCHAR (128) NOT NULL,
    [PasswordChangedDate] DATETIME NULL,
    [PasswordSalt] NVARCHAR (128) NOT NULL,
    [PasswordVerificationToken] NVARCHAR (128) NULL,
    [PasswordVerificationTokenExpirationDate] DATETIME NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
    );


    CREATE TABLE [dbo].[webpages_OAuthMembership] (
    [Provider] NVARCHAR (30) NOT NULL,
    [ProviderUserId] NVARCHAR (100) NOT NULL,
    [UserId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Provider] ASC, [ProviderUserId] ASC)
    );

    CREATE TABLE [dbo].[webpages_Roles] (
    [RoleId] INT IDENTITY (1, 1) NOT NULL,
    [RoleName] NVARCHAR (256) NOT NULL,
    PRIMARY KEY CLUSTERED ([RoleId] ASC),
    UNIQUE NONCLUSTERED ([RoleName] ASC)
    );

    CREATE TABLE [dbo].[webpages_UsersInRoles] (
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [fk_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId]),
    CONSTRAINT [fk_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[webpages_Roles] ([RoleId])
    );