Security code

--logins are stored in master
Use master

--create sql server login
Create login GrantApplicantLogin with password='P@ssw0rd1', default_database=Community_Assist

--use community_assist
Use Community_Assist
go
--crate schema 
Create Schema ApplicantSchema
Go
--create user 
Create user GrantApplicant for login GrantApplicantLogin 
with default_schema= ApplicantSchema
Go
--create role
Create role ApplicantRole
Go

--assign permissions on objects to role
Grant Select, exec on schema::ApplicantSchema to ApplicantRole

--add the user to the role
alter Role Applicantrole add member GrantApplicant
go
--Create procedure as part of procedure
create proc ApplicantSchema.usp_GrantHistory
@personKey int
As
Select GrantRequestDate, GrantTypeName, GrantRequestExplanation,
GrantRequestAmount
From GrantRequest
inner join  GrantType
on GrantRequest.GrantTypeKey=GrantType.GrantTypeKey
where personkey = @personkey
go
--this is a view for the human resources schema
--we build this using the graphical tools
Create view HumanResourcesSchema.vw_Employee
as
Select PersonLastName, PersonFirstName,
PersonEmail, EmployeeHireDate, EmployeeannualSalary
From Person p
inner join Employee e
on p.personkey=e.personKey
go