Basic Security Script

Use Automart
go
--create schema for managers
Create Schema manager
--create an object that belongs to the schema Manager
Go
Create view manager.vw_LocationSummary
 As
 Select LocationName, count(distinct vs.VehicleServiceId) as [Count],
 sum(dbo.fx_GetTotalDue(ServicePrice, DiscountPercent)) as Total
 From Customer.AutoService a
 inner join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 inner Join Employee.VehicleService vs
 on vsd.VehicleServiceID=vs.VehicleServiceID
 inner Join Customer.Location loc
 on loc.LocationID=vs.LocationID
 Group by LocationName
 go
 --Create a role for Managers
 create role MangagerRole
 Go
 --provide permission for manager role
 Grant select, update on Schema::manager to ManagerRole

--create a login for managers
Create Login ManagerLogin with password='P@ssw0rd1'

--create a user in automart that is mapped to that login
Create user ManagerUser for Login ManagerLogin

--add the user to the role
exec sys.sp_addrolemember 'managerRole', 'ManagerUser'

--now login you should only see the objects that belong to the schema Manager
--and only have the permissions assigned to the role