last lab

--1 Create three logins called ann, burt, and chuck.
--The corresponding passwords are a1b2c3d4e5, d4e3f2g1h0, and f102gh285, respectively.
--The default database is the sample database. After creating the logins, check their existence using the system catalog.


CREATE LOGIN ann
WITH PASSWORD = 'a1b2c3d4e5'

CREATE LOGIN burt
WITH PASSWORD = 'd4e3f2g1h0'

CREATE LOGIN chuck
WITH PASSWORD = 'f102gh285'

--2 Create three new database usernames for the logins in exercise 4. The new names are s_ann, s_burt, and s_charles.

CREATE USER s_ann FOR LOGIN [ann]
CREATE USER s_burt FOR LOGIN [burt]
CREATE USER s_chuck FOR LOGIN [chuck]

--3.Create a new user-defined database role called managers and add three members to the role.
--After that, display the information for this role and its members.

CREATE ROLE manager
ALTER ROLE manager ADD MEMBER s_ann
ALTER ROLE manager ADD MEMBER s_burt
ALTER ROLE manager ADD MEMBER s_chuck

--4. Using the GRANT statement, allow the user s_burt to create tables and the
--user s_ann to create stored procedures in the sample database.

GRANT CREATE TABLE TO s_burt
GRANT CREATE PROCEDURE TO s_ann

--5. Using the GRANT statement, allow the user s_charles to update the columns lname and fname of the employee table.

GRANT UPDATE(emp_lname , emp_fname)
ON employee TO s_chuck

--6. Using the GRANT statement, allow the users s_burt and s_ann to read the values from the columns emp_lname and emp_fname
--of the employee table. (Hint: Create the corresponding view first.)

CREATE VIEW hehe AS
SELECT emp_lname , emp_fname FROM employee
GRANT SELECT  ON hehe TO s_burt

--7. Using the GRANT statement, allow the user-defined role managers to insert new rows in the project table.

GRANT INSERT ON project TO manager


--8. Revoke the SELECT rights from the user s_burt.

REVOKE SELECT ON hehe FROM s_burt

--9. Using Transact-SQL, do not allow the user s_ann to insert the new rows in
--the project table either directly or indirectly (using roles).

DENY INSERT TO s_ann



--10. Display the existing information about the user s_ann in relation to the sample database.
--(Hint: Use the system procedure sp_helpuser.)

EXEC sp_helpuser 's_ann'