Logins, Users, Schema, Roles

---sql server login
--schema dbo as ownership
--roles
--user
--logins give you connection access to the server but not much else
Create login emp with password='p@ssw0rd1', default_database =CommunityAssist


Use CommunityAssist
--users are database specific and are always associated with a server login
Create user employ for login emp 
go
--schemas are groups of ownership
Create schema Employee

--create an object that belongs to the schema
Create view Employee.Donors
as
Select LastName, firstname,
DonationDate, Donationamount
From Person p
inner Join Donation d
on p.PersonKey=d.PersonKey

--add the user to the schema
Alter user employ with default_schema=Employee

--give the user select permissions on all the objects 
--in the schema 
Grant select on  Schema::Employee to Employ 

--A role is a collection of permissions
Create Role humanResources
Grant Select, update on Employee to humanResources
Grant Select, update on Person to humanResources

/* 
these are all the database permissions you can grant
Select 
Insert
UPDATE
Delete
Create
Alter
exec
drop
*/

--add an use to the role using a stored procedure
exec sp_addRoleMember  humanResources, employ
go

--this drops a user from the role
exec sp_droprolemember humanResources employ