How to design a user/role schema in a SQL Server database?

Question

I want to design a user/role system:

The users have a name and a password and then the user can have several roles like Admin.

For this I created a schema like this:

Users:

CREATE TABLE [dbo].[Users]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [password] [nvarchar](50) NULL,

    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([id] ASC)
)

Roles:

CREATE TABLE [dbo].[Roles]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

user_roles:

CREATE TABLE [dbo].[User_Roles]
(
    [id] [int] NOT NULL,
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

My question is: should I use foreign keys User_Roles.User_id -> User.Id

If yes why?

Answer 1

Not quite sure what you mean, but...

• User_Roles should have 2 columns only User_id and Role_id
  Both of these form the Primary Key
• You do not need an extra id column User_Roles
• User_id is a foreign key to Users.id
• Role_id is a foreign key to Roles.id

Edit: Now I understand. Yes, always use foreign keys

Also...

• if password is nvarchar(50), this implies plain text. This is bad.
• if you have duplicate name values in Users, how do you know which user is which?
  Especially if they have the same password (which will happen because we meatsacks are stupid)

Edit after comment after primary key creation...

CREATE TABLE [dbo].[User_Roles]
(
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([User_id], [Role_id]),
    CONSTRAINT [UQ_ReversePK] UNIQUE ([Role_id], [User_id])
)

Answer 2

Spring Security makes this recommendation:

create table users(
    username varchar_ignorecase(50) not null primary key,
    password varchar_ignorecase(50) not null,
    enabled boolean not null
);

create table authorities (
    username varchar_ignorecase(50) not null,
    authority varchar_ignorecase(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);