Security Stored Procedures

Select * from Position
--Driver access to own information
--personal information
--shifts driven
--pay per month
--pay per year
--pay per shift
--select route schedules
--select Bus Barn
go
Create schema DriverSchema
go
Create proc DriverSchema.usp_ViewEmployeeInfo
@EmployeeID int
As
Select e.EmployeeKey [Employee Number], 
EmployeeLastName [Last Name], 
EmployeeFirstName [First Name], 
EmployeeAddress [Address], 
EmployeeCity [City], 
EmployeeZipCode [Zip Code], 
EmployeePhone [Phone], 
EmployeeEmail [Email], 
EmployeeHireDate [Hire Date],
PositionName [Position],
EmployeeHourlyPayRate [Pay Rate]
From Employee e
inner Join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner Join Position p
on p.PositionKey=ep.PositionKey
Where e.EmployeeKey=@EmployeeID

exec DriverSchema.usp_ViewEmployeeInfo 1

Go
create proc DriverSchema.usp_shiftsDrive
@month int,
@year int,
@EmployeeID int
As
Select
[BusScheduleAssignmentDate] [Date],
[BusDriverShiftName] [Shift],
[BusRouteKey] [Route],
[BusKey] [Bus],
[BusDriverShiftStartTime] [Start],
[BusDriverShiftStopTime] [Stop],
DateDiff(hh,[BusDriverShiftStartTime],[BusDriverShiftStopTime]) [hours]
From [dbo].[BusScheduleAssignment] bsa
inner Join BusDriverShift bs
on bsa.BusDriverShiftKey=bs.BusDriverShiftKey
Where Year([BusScheduleAssignmentDate])=@Year
And Month([BusScheduleAssignmentDate])=@Month
And bsa.EmployeeKey=@EmployeeID

exec DriverSchema.usp_shiftsDrive
@Year=2014,
@Month=7,
@EmployeeID=1
go
Create Proc DriverSchema.usp_UpdatePersonal
@LastName nvarchar(255),
@FirstName nvarchar(255),
@Address nvarchar(255),
@City nvarchar(255)='Seattle',
@Zip nchar(5),
@Phone nchar(10),
@EmployeeId int
As
if exists
  (Select EmployeeKey from Employee where EmployeeKey=@EmployeeID)
Begin
Update Employee
Set [EmployeeLastName]=@Lastname,
[EmployeeFirstName]=@firstName,
[EmployeeAddress]=@Address,
[EmployeeCity]=@City,
[EmployeeZipCode]=@zip,
[EmployeePhone]=@phone
Where EmployeeKey = @EmployeeID
return 1
End
Else
Begin
Declare @msg nvarchar(30)
Set @msg='Employee Doesn''t Exist'
Print @msg
return 0
end
go
exec DriverSchema.usp_UpdatePersonal
@LastName = 'Kenner-Jones' ,
@FirstName = 'Susanne',
@Address = '234 Some Other Street',
@City ='Seattle',
@Zip= '98100',
@Phone='2065554312',
@EmployeeId =600

Select * From Employee

Create login KJSusa with password='@Passw0rd1'

Use Metroalt 
Create user KJSusa for login KJSusa with default_Schema=DriverSchema

Create role DriverRole

Grant exec, select on Schema::DriverSchema
to DriverRole

exec sp_addrolemember 'DriverRole', 'KJSusa'