Security Procedures

Create Schema ManagerSchema
Go
Create proc ManagerSchema.usp_UpdateEmployee
@EmployeeKey int, 
@EmployeeLastName nvarchar(255), 
@EmployeeFirstName nvarchar(255), 
@EmployeeAddress nvarchar(255), 
@EmployeeCity nvarchar(255), 
@EmployeeZipCode nchar(5), 
@EmployeePhone nchar(10), 
@EmployeeEmail nvarchar(255), 
@PositionName nvarchar(255),
@EmployeeHourlyPayRate decimal(5,2),
@OrignalDatePositionAssigned Date,
@EmployeePositionDateAssigned date
As
Declare @PositionKey int
Select @Positionkey = PositionKey from Position
Where PositionName = @PositionName
if @positionKey is null
Begin
print 'Invalid position'
return -1
End
Begin tran
Begin try
Update Employee
Set [EmployeeLastName] = @EmployeeLastName,
[EmployeeFirstName] = @EmployeeFirstName,
[EmployeeAddress]=@EmployeeAddress,
[EmployeeCity]=@EmployeeCity,
[EmployeeZipCode]=@EmployeeZipCode,
[EmployeePhone]=@EmployeePhone,
[EmployeeEmail]=@EmployeeEmail
Where EmployeeKey = @EmployeeKey

Update EmployeePosition
Set [PositionKey]=@PositionKey,
[EmployeeHourlyPayRate]=@EmployeeHourlyPayRate,
[EmployeePositionDateAssigned]=@EmployeePositionDateAssigned
Where EmployeeKey = @EmployeeKey
and EmployeePositionDateAssigned = @OrignalDatePositionAssigned

Commit Tran
End try
Begin Catch
Rollback tran
Print error_Message()
return -2
End Catch


Select * from Employee

exec ManagerSchema.usp_UpdateEmployee
@EmployeeKey = 1, 
@EmployeeLastName ='Summers', 
@EmployeeFirstName='Susanne', 
@EmployeeAddress='28 Elm Ave.', 
@EmployeeCity='Seattle', 
@EmployeeZipCode='98100', 
@EmployeePhone='2065554312', 
@EmployeeEmail='Elizabeth.Adams16@metroalt.com', 
@PositionName='Lawyer', 
@EmployeeHourlyPayRate=100.00, 
@OrignalDatePositionAssigned='4/14/1998', 
@EmployeePositionDateAssigned='7/28/2016'

Select * from EmployeePosition

create Unique index ix_UniqueEmail on Employee (EmployeeEmail)

--add a driver to a route for a day
go
Create proc managerSchema.usp_AssignShift
 @BusDriverShiftKey int, 
 @EmployeeKey int, 
 @BusRouteKey int, 
 @BusScheduleAssignmentDate date, 
 @BusKey int
 As
 Begin tran
 Begin try
 Insert into BusScheduleAssignment (
BusDriverShiftKey, 
EmployeeKey, 
BusRouteKey, 
BusScheduleAssignmentDate, 
BusKey)
Values(
@BusDriverShiftKey, 
@EmployeeKey, 
@BusRouteKey, 
@BusScheduleAssignmentDate, 
@BusKey)

Commit Tran
End Try
Begin Catch
Rollback tran
print Error_Message()
return -1
End Catch



Exec [ManagerSchema].[usp_AssignShift]
@BusDriverShiftKey=2, 
@EmployeeKey=6, 
@BusRouteKey=127, 
@BusScheduleAssignmentDate='7/29/2016', 
@BusKey=43

Select * from [dbo].[BusScheduleAssignment]
Select * from Busroute

Select * from EmployeePosition where EmployeeKey=6
go
Create Proc managerSchema.usp_TotalHoursDateRange
@EmployeeKey int,
@startDate Date,
@EndDate Date
AS

Select EmployeeKey, sum(dateDiff(hour,[BusDriverShiftStartTime],[BusDriverShiftStopTime])) [Total Hours]
From BusDriverShift bs
Inner join BusScheduleAssignment bsa
on bs.BusDriverShiftKey=bsa.BusDriverShiftKey
Where EmployeeKey=@EmployeeKey
And [BusScheduleAssignmentDate] between @startDate and @EndDate
Group by EmployeeKey
go
exec [ManagerSchema].[usp_TotalHoursDateRange]
@EmployeeKey= 6, 
@startDate ='6/1/2014', 
@EndDate='6/8/2014'

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

Views for Reports

--List of Donors
--Amount allocated to each grant type
--total donations by year and month
--Total grants by year and month (count, total alloctated)
--Average request per grant type
--total requests vs total allocated (by year month--also by type)

use Community_Assist

Go
Create view vw_DonorContact
As
Select PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
Where Personkey in (Select PersonKey from Donation)

Go
Alter view vw_DonorContactb
As
Select Distinct PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
inner join Donation
on person.PersonKey = Donation.PersonKey
go

Select * from vw_DonorContactb order by LastName

Select * from Donation
go
Create view vw_TotalDonationsByYearMonth
as
Select Year(DonationDate) [Year], 
DateName(month, DonationDate) [MonthName],
format(sum(DonationAmount),'$ #,###.00') Total From Donation
Group by Year(donationdate), DateName(month, DonationDate)
go
Select * from vw_TotalDonationsByYearMonth order by Year

go
Create view vw_TotalGrantsByYearMonth
As
Select Year(GrantReviewDate) [Year],
DateName(month, GrantReviewDate) [Month],
Sum(GrantAllocationamount) Total
From GrantReview
Group by Year(GrantReviewDate), Datename(Month,GrantReviewDate)

go
Create view vw_TotalAllocatedByGrantType
As
Select GrantTypeName, sum(GrantAllocationAmount) Total
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go
Create view vw_CountByGrantType
As
Select GrantTypeName, Count(*) [Count]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go

Create view vw_RequestvsAllocationByGrantType
AS
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName
go
Create proc usp_RequestvsAllocationByGrantType
@GrantTypeName nvarchar(255)
As
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
where GrantTypeName=@GrantTypeName
Group by GrantTypeName


exec usp_RequestvsAllocationByGrantType 'Child Care'

Snapshot

Create database Community_AssistSnapshot
on
(name='Community_Assist', 
filename=
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Community_Assist.ds')
as
Snapshot of Community_Assist

Use Community_AssistSnapshot
Select * from Person

Use Community_Assist
Update Person
Set PersonFirstName = 'jason'
Where Personkey=1

Select * From Person

Backup Restore

--full backup of the database
Backup database Community_Assist
To disk= 'C:\Backups\Community_Assist.Bak'
with init

--create table after full backup and insert a record
Create table Test2
(
 TestKey int identity(1,1) primary key,
 TestDate DateTime default GetDate(),
 TestDescription nvarchar(255)
)

Insert into Test2 (TestDescription)
values('Table added after full backup.')

--then we do the differential backup
Backup Database Community_Assist
To Disk='C:\Backups\Community_Assist.Bak'
with differential



Insert into Test2 (TestDescription)
values('This record added after differential')

--now backup the log
use Master
Backup log Community_Assist
to disk ='C:\Backups\Community_Assistlog.Bak'
with norecovery, no_truncate
-- restore full backup (file  1)
Restore Database Community_Assist
From disk='C:\Backups\Community_Assist.Bak'
with norecovery, file=1

--Resore Differential backup (file 2)
Restore Database Community_Assist
From disk='C:\Backups\Community_Assist.Bak'
with norecovery, file=2

--restore the log 
Restore Log Community_Assist
From disk='C:\Backups\Community_Assistlog.Bak'
with recovery

use Community_Assist
Select * from Test2
/*
Syntax for restoring to a moment in time
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
*/

Table Partitioning

/*PARTITIONING EXAMPLE
I am going to use the Employee table in metroalt for this partition
though I am not going to make the partitions in MetroAlt itself,
Rather I will make a new database and a new table and copy the data
from metroAlt into that database.
For the partitions we will use 1995-1999, 2000-2004, 2004-2009, 2010 forward
Our database will need to have five file groups.
Here's the pattern:
Create database PartitionTest
go
Alter database PartitionTest
Add FileGroup Sales2005;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2005',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2005File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )
 To filegroup Sales 2005
*/


if exists
   (Select name from sys.Databases 
   where name = 'EmployeePartition')
Begin
Drop Database EmployeePartition
end
Go
-- create the database.
Create database EmployeePartition

go
--add the first file group and file
alter database EmployeePartition
Add Filegroup Employees1995Group
Go
Alter database EmployeePartition
Add File
(name='Employees1995',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees1995File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to Filegroup Employees1995Group

go

alter database EmployeePartition
Add Filegroup Employees2000Group
Go
Alter database EmployeePartition
Add File
(name='Employees2000',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2000File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2000Group
go

alter database EmployeePartition
Add Filegroup Employees2005Group
Go
Alter database EmployeePartition
Add File
(name='Employees2005',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2005File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To Filegroup Employees2005group
go

alter database EmployeePartition
Add Filegroup Employees2010Group
Go
Alter database EmployeePartition
Add File
(name='Employees2010',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2010File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2010Group

go

alter database EmployeePartition
Add Filegroup Employees2015Group
Go
Alter database EmployeePartition
Add File
(name='Employees2015',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2015File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To filegroup Employees2015Group

/*Next we want to create the partion function and
the partition scheme.
*/

Use EmployeePartition

Create Partition Function Fx_Hiredate (date)
As range left
For values('19941231', '19991231','20041231','20091231')

Go

--create the partition schema
Create Partition scheme sch_HireDate
As Partition fx_HireDate
to (Employees1995Group, Employees2000Group, Employees2005Group, Employees2010Group, Employees2015Group)

--now create the table that uses the partition schema
CREATE TABLE [dbo].[Employee](
 [EmployeeKey] [int],
 [EmployeeLastName] [nvarchar](255),
 [EmployeeFirstName] [nvarchar](255),
 [EmployeeAddress] [nvarchar](255),
 [EmployeeCity] [nvarchar](255),
 [EmployeeZipCode] [nchar](5),
 [EmployeePhone] [nchar](10),
 [EmployeeEmail] [nvarchar](255),
 [EmployeeHireDate] [date] 
) on sch_HireDate(EmployeeHireDate)

--insert into the table
Insert into Employee(EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate)
Select EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate
From MetroAlt.Dbo.Employee

Select * from Employee

--1995-1999
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=2

--2000-2004
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=3

--2005-2009
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=4

--2010-2014

Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=5