Xml and Logins

Alter table ServiceGrant
Add GrantDetails xml

Insert into ServiceGrant(GrantAmount,
GrantDate,
PersonKey,
ServiceKey,
EmployeeKey,
GrantDetails)
Values ( '1200',GETDATE(), 2, 1, 1,
'

The person is very hungry


Consider half this amount

')

Select * from ServiceGrant

--the last part starting with "for" is what you need for
--the final assignment
Select LastName, FirstName, contactInfo
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
Where ContactTypeKey=6
Order By LastName for xml raw('contact'), Root('emailList'), elements

--this is a sql server login
--you must first enable mixed logins
--by going to the server properties
--clicking the security tab
--clicking the checkbox by SqlServer and Windows logins
--and then restarting the server
Create login stevec
with password ='$passw0rd1',
default_database = CommunityAssist
go

Create schema manager --a schema is a collection of related objects
--it has to do with ownership
go
--this stored procedure belongs to the schema manager
Create proc manager.usp_EmployeeGrants
@EmployeeID int
As
Select COUNT(GrantKey) as total From ServiceGrant
Where EmployeeKey = @EmployeeID
GO
--create a user that is a member of the schema
Create user stevec for login stevec with default_schema=manager

--grant executable permissions on the schema to the user
Grant exec on Schema::manager to stevec