Sql Server Creacion de usuarios y permisos

query.md

Sql Server, create users and roles

create database

CREATE DATABASE Book
GO

create login for the admin user

CREATE LOGIN AdminBook WITH PASSWORD = 'Bookapp1'
GO

create a login for the reading user

CREATE LOGIN BookClient WITH PASSWORD = 'Bookapp1'
GO

use database

USE Book
GO

create admin and client users

CREATE USER AdminBook FOR LOGIN AdminBook
CREATE USER BookClient FOR LOGIN BookClient
GO

add the permission to the db_owner admin user who has full access to rhe databse

ALTER ROLE db_owner ADD MEMBER AdminBook
GO

add the write and read roles for the client user

ALTER ROLE db_datareader ADD MEMBER BookClient
ALTER ROLE db_datawriter ADD MEMBER BookClient
GO

---

Create Table

CREATE TABLE Stage
(
  id_stage INT IDENTITY(1,1) CONSTRAINT pk_id_stage PRIMARY KEY,
  name_stage NVARCHAR(50) NOT NULL
)

CREATE TABLE SubStage
(
  id_sub_stage INT IDENTITY(1,1) CONSTRAINT pk_id_sub_stage PRIMARY KEY,
  name_stage NVARCHAR(50) NOT NULL,
  stage_id INT CONSTRAINT fk_stage_id NOT NULL,
  FOREIGN KEY (stage_id) REFERENCES dbo.Stage (id_stage)
)

Insert

INSERT INTO Stage VALUES ('Quarter-Finals')
INSERT INTO Stage VALUES ('Semi-Finals')
INSERT INTO Stage VALUES ('Final')

INSERT INTO SubStage VALUES ('Quarterfinal First', 1)
INSERT INTO SubStage VALUES ('Quarterfinal Second', 1)
INSERT INTO SubStage VALUES ('Quarterfinal Third', 1)
INSERT INTO SubStage VALUES ('Quarterfinal Forth', 1)
INSERT INTO SubStage VALUES ('Semifinal First', 2)
INSERT INTO SubStage VALUES ('Semifinal Second', 2)
INSERT INTO SubStage VALUES ('Final Game', 3)
INSERT INTO SubStage VALUES ('Third place', 3)

---

Scaffold

$ dotnet ef dbcontext scaffold "Server=localhost; Database=my_db; User=my_user-sa; Password=my_password_1;"
Microsoft.EntityFrameworkCore.SqlServer --context-dir Data -c DataContext --output-dir Models