Creating Schema

In class this morning we created two schema, one called Venue and one called artist. We created a stored procedure that belonged to the Venue Schema. Then we created a role that assigned the permission exec on the Venue schema. We created a new user and login who belonged to the schema and was a member of the VenueRoll roll. Then we logged in as that user. The new user could only access that one stored procedure. Everything else generated an no-permissions error

Here is the SQL. Some of this we did with the GUI

--Create Schemas
--schemas are about ownership of database objects

Create schema Customer
Create Schema Artist


---Some notes on things for the Venue schema
-- they need to see own information
--Venue information--show information
--artist information
--ticketing information

--Select
--Insert--new shows
--Update--change their own Venue information

--this creates a stored procedure that belongs to the 
--Venue Schema
Go
Create proc Venue.usp_ShowList
@VenueID int --user provided parameter
As
Select VenueName [Venue], 
ArtistName [Artist],
TourName [Tour Name],
ShowDate [Date],
ShowTime [Time],
ShowPosition [Position],
ShowDescription [Description]
From Venue v
Inner join Show s
On v.VenueID=s.VenueID
inner join Artist a
on a.ArtistID=s.ArtistID
Where v.VenueID=@VenueID

--this tests the stored procedure
exec Venue.usp_ShowList 
@VenueID=2

--this creates a role. Roles are collections of 
--permissions
Create Role venueRole

--this grants the permission to execute stored procedures
--that belong to the schema venu
Grant exec  on Schema::Venue to VenueRole