Logins, Users, Permissions

Use communityAssist
go
--this is a server level login
--you always log in to the server first
--this gives no permissions but connection
Create Login EmployeeLogin with password='P@ssword1', 
default_database=communityAssist
go
--a schema is a collection of objects
Create schema EmployeeSchema
go

--logins are server level, users are database level. Database
--permissions are granted at the user level
--we are also addeding the user to the EmployeeSchema
Create User EmployeeUser for login EmployeeLogin with default_schema =EmployeeSchema

--here we create a couple of views that belong
--to the employeeSchema
Go
Create view EmployeeSchema.vw_DonationTotals
As
Select Month(DonationDate) as [Month],
Year(donationDate)as [Year],
sum(donationAmount) as total
From Donation
group by Year(donationDate), Month(donationDate)
Go
Create view EmployeeSchema.vw_Donors
As
Select LastName, FirstName, donationDate, donationAmount
From person p
inner join donation d
on p.personkey=d.personkey
go
--now we create a role. roles are collections of permissions
Create Role EmployeeRole
go
--here we assign some permissions to the Employeerole
Grant Select on Schema::EmployeeSchema to EmployeeRole
Grant select on Person to EmployeeRole
Grant insert on ServiceGrant to EmployeeRole

--Now we add the user to the role

exec sp_addrolemember 
@membername='EmployeeUser',
@RoleName='EmployeeRole'

/*************************************
to login as the new user you must first make sure
that the server has SQL Server and Windows Authentication
enabled. to do that
1. Right click on the server (top level)
2. Select Properties
3. Select Security
4. Click the radio button beside SQL Server and Window's Authentication
5. Click OK. You will recieve a warning that changes won't take effect
until the server is resarted.
6. Click OK
7. right click on server again
8.Choose restart, you will have to say yes to the restart twice
9. Once the server has restarted click on connect at the top of the
object explorer
10. in the login dialog box change the windows authentication 
to sql server authentication
11. Enter the login name and the password
***********************************************/