Logins and users

--admin sql
--authentication--are you who you say you are
 --login to the server
 --login mapped to a user at the database level
 --user is given database permissions
 --windows authentication
 --sql server authentication --user name password
--authorization--what can do

--Here is a windows login
Use Master
USE [master]
GO

/****** Object:  Login [NT AUTHORITY\SYSTEM]    Script Date: 3/6/2013 11:26:39 AM ******/
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--a sql server login
Create login EmployeeLogin with password='P@ssw0rd1', default_database=CommunityAssist

Use CommunityAssist
--schema are collections of objects
Go
Create schema EmployeeSchema 
--create a user that maps to that login and uses the schema
Create user EmployeeUser for Login EmployeeLogin with default_schema=employeeschema

Create role EmployeeRole

--assign permissions to the role
Grant select on Donation to EmployeeRole
Grant Select on Employee to EmployeeRole
Grant update on Donation to EmployeeRole
Grant exec on usp_newDonor to EmployeeRole
Grant exec on usp_ReturnDonorInfo to EmployeeRole

--assign the user to the role
exec sp_addrolemember 'employeerole', 'employeeUser'

Select * from PersonContact

Go
--create an object that belongs to the schema
Create view EmployeeSchema.GrantsView
As
Select ServiceName, sum(GrantAmount)as Total
From [Service]
inner Join ServiceGrant
on [Service].ServiceKey=ServiceGrant.ServiceKey
Group by ServiceName
go
Grant Select on schema::EmployeeSchema to Employeeuser