USE master;

DROP TABLE TEST_RAICH_ERLAUBT;
DROP TABLE TEST_RAICH_VERBOTEN;
DROP LOGIN raich_login;
DROP USER raich_user;
DROP ROLE raich_role;


SELECT * FROM TEST_RAICH_ERLAUBT;
SELECT * FROM TEST_RAICH_VERBOTEN;
-- ---------------------------------------------------
-- - LOGIN-SKRIPT ------------------------------------
-- ---------------------------------------------------
CREATE LOGIN raich_login WITH PASSWORD = 'raich_login';
GO

-- ---------------------------------------------------
-- - BENUTZER ERZEUGEN -------------------------------
-- ---------------------------------------------------
CREATE USER raich_user FROM LOGIN  raich_login;
GO

-- ---------------------------------------------------
-- - SCHEMA ERZEUGEN ---------------------------------
-- ---------------------------------------------------
CREATE SCHEMA raich_schema;
GO

-- ---------------------------------------------------
-- - TABELLE ERZEUGEN --------------------------------
-- ---------------------------------------------------
CREATE TABLE raich_schema.TEST_RAICH_ERLAUBT (
    spalte INT
);
CREATE TABLE raich_schema.TEST_RAICH_VERBOTEN (
    spalte INT
);

INSERT INTO raich_schema.TEST_RAICH_ERLAUBT VALUES(1);
INSERT INTO raich_schema.TEST_RAICH_VERBOTEN VALUES(1);


-- ---------------------------------------------------
-- - ROLLE ERZEUGEN ----------------------------------
-- ---------------------------------------------------
CREATE ROLE raich_role;
GO

-- ---------------------------------------------------
-- - ROLLE EINEM BENUTZER ZUWEISEN -------------------
-- ---------------------------------------------------
EXEC sp_addrolemember @rolename = 'raich_role', @membername = 'raich_user';
GO

-- ---------------------------------------------------
-- - BERECHTIGUNGEN VERGEBEN -------------------------
-- ---------------------------------------------------
GRANT SELECT, INSERT ON OBJECT::raich_schema.TEST_RAICH_ERLAUBT TO raich_role;
GO
GRANT DENY ON OBJECT::raich_schema.TEST_RAICH_ERLAUBT TO raich_role;
GO


-- ---------------------------------------------------
-- - ALS USER AUSFÜHREN ------------------------------
-- ---------------------------------------------------
EXECUTE AS USER = 'raich_user';
GO
SELECT * FROM raich_schema.TEST_RAICH_ERLAUBT;
-- --
REVERT;
-- --
EXECUTE AS USER = 'raich_user';
GO
SELECT * FROM raich_schema.TEST_RAICH_VERBOTEN;
-- --
REVERT;