Security

/****************************
login  Authentication and Authorization
Login --server user mapped to the login and is for a database
Windows Authentication--Active directory
Sql Server Authentication--password username

Roles --Collections of Permissions
Schema -- ownership of a collection of objects

Community_Assist
Admin
Reviewers SELECT UPDATE DELETE INSERT DROP CREATE ALTER EXEC

Volunteers
Clients
General--public
Donors

What kinds of views would people have
Stored Procedures, How interact

Role
Schema
*/

--schema
use Community_Assist
Go
Create schema ClientSchema
go
Create view ClientSchema.vw_GrantType
As
Select * from GrantType
go
Select * from ClientSchema.vw_GrantType
Go
Create proc ClientSchema.usp_grantStatus
@PersonKey int
As
Select GrantTypeName [GrantType],
GrantRequestDate [Date],
GrantRequestExplanation [Explanation],
GrantRequestAmount Amount,
GrantRequestStatus [Status],
GrantAllocationAmount Allocation
From GrantType gt
inner join GrantRequest req
on gt.GrantTypeKey=req.GrantTypeKey
inner join GrantReview rev
on req.GrantRequestKey=rev.GrantRequestKey
Where personkey = @PersonKey

exec ClientSchema.usp_grantStatus 1

Create role ClientRole

Grant Select, execute on Schema::ClientSchema to ClientRole

Create Role GeneralUserRole
Grant Select on GrantType to GeneralUserRole
Grant Select on vw_Donations to GeneralUserRole
Grant insert on Person to GeneralUserRole

Create login Jody with password='P@ssword1', 
default_database=Community_Assist

Create user Jody for login jody with default_schema=ClientSchema
exec sp_AddRoleMember 'ClientRole','jody'