-- Make sure to replace the servername with the name of your server...
-- and make sure that you've created these windows accounts: Bill and Ted

USE master
GO

DROP LOGIN [RoadRunner64\Bill]
DROP LOGIN [RoadRunner64\Ted]
go

CREATE LOGIN [RoadRunner64\Bill]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks

CREATE LOGIN [RoadRunner64\Ted]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
GO

Use AdventureWorks
GO
CREATE USER Bill For LOGIN [RoadRunner64\Bill]
CREATE USER Ted For LOGIN [RoadRunner64\Ted]
GO

-- Create object in dbo schema
CREATE TABLE dbo.NewProduct
(ProductID INT IDENTITY,
 ProductName NVARCHAR(50))
GO

-- Create a schema
CREATE SCHEMA MarketingSchema
  CREATE TABLE Adverts
  (AdvertID INT IDENTITY,
   ProductID INT,
   AdCopy NVARCHAR(50))

  GRANT ALL PRIVILEGES ON Adverts TO Bill
	-- What does ALL imply in SQL Server 2005?
	-- THE SAME AS ALL in 2000 - S, U, I, D, References
	-- Good message as well: 
	-- The ALL permission is deprecated and maintained only 
	-- for compatibility. It DOES NOT imply ALL permissions 
	-- defined on the entity. 
GO

-- If you're working outside of the schema definition... 
-- two part naming required
GRANT ALL PRIVILEGES ON Adverts TO Bill

GRANT ALL PRIVILEGES ON MarketingSchema.Adverts TO Bill
go

EXEC sp_helprotect Adverts, Bill
go

-- Set a user's default schema
ALTER USER Bill
WITH DEFAULT_SCHEMA = MarketingSchema
go

select user_name()

EXECUTE AS USER = 'Bill'

select user_name()

SELECT * FROM Purchasing.PurchaseOrderHeader

SELECT * FROM MarketingSchema.Adverts
SELECT * FROM Adverts

INSERT Adverts (ProductID) VALUES (1)

SELECT * FROM MarketingSchema.Adverts
SELECT * FROM Adverts

REVERT

go

EXECUTE AS USER = 'Ted'

SELECT * FROM Purchasing.PurchaseOrderHeader

SELECT * FROM MarketingSchema.Adverts
SELECT * FROM Adverts

INSERT Adverts (ProductID) VALUES (1)

SELECT * FROM MarketingSchema.Adverts
SELECT * FROM Adverts

REVERT
go

-- Clean up...
DROP USER Bill
DROP USER Ted
DROP TABLE dbo.NewProduct
DROP TABLE MarketingSchema.Adverts
DROP SCHEMA MarketingSchema