create database securitydb
use securitydb

create table personeel (id int primary key identity(1,1),
naam varchar(30), loon money)

insert into personeel (naam, loon)
values ('Jan', 1000), ('Piet',2000), ('Joris',3000),
('Corneel',4000)

select * from personeel

create login [servertest\k3] from windows

create user [k3] from login [servertest\k3]

--exec sp_addrolemember 'db_datareader','k3'
alter role db_datareader add member k3

create login [servertest\kathleen] from windows

deny connect sql to [servertest\kathleen]

create login [servertest\kristel] from windows

create user [kristel] from login [servertest\kristel]

deny select on dbo.personeel(loon) to kristel

create login beheer with password = 'Rr00tt'
-- exec sp_addsrvrolemember 'beheer','dbcreator'
alter server role dbcreator add member beheer

use securitydb2

grant connect to guest

--exec sp_addrolemember 'db_datareader','guest'
alter role db_datareader add member guest

--exec sp_addsrvrolemember 'beheer', 'securityadmin'
alter server role securityadmin beheer

create view vw_antwerpen
as
select * from dbo.klant where gemeente = 'antwerpen'

select * from vw_antwerpen

grant select on vw_antwerpen to geb01

--exec sp_addrolemember 'db_ddladmin', 'geb01'
alter role db_ddladmin add member geb01

drop login geb01

sp_change_users_login 'report'

create database securitydb3

use securitydb3

create schema lezen

create schema schrijven

create table lezen.product (id int primary key identity(1,1),
naam varchar(30), prijs money)

create table lezen.klant(id int primary key identity(1,1),
naam varchar(30))

create table schrijven.bestelling (klantid int references lezen.klant(id),
productid int references lezen.product(id))

create role lezers

grant select on schema::lezen to lezers

grant select, insert, update, delete on schema::schrijven to lezers

create application role approle with password = 'Rr00tt'

grant select, insert, update, delete on schema::lezen to approle