
CREATE LOGIN FRED WITH PASSWORD = 'StrongPW1'
CREATE LOGIN BOB WITH PASSWORD = 'StrongPW1'
CREATE LOGIN ALICE WITH PASSWORD = 'StrongPW1'
GO

USE pubs
GO

-- old SQL Server 2000 behavior
sp_adduser 'FRED', 'FRED'
sp_adduser 'BOB', 'BOB'
sp_adduser 'ALICE', 'ALICE'
GO

-- or SQL Server 2005 with schemas
-- do not do both
CREATE USER FRED FOR LOGIN FRED
CREATE USER BOB FOR LOGIN BOB
CREATE USER ALICE FOR LOGIN ALICE
GO

GRANT CREATE TABLE TO FRED
GRANT CREATE TABLE TO ALICE
GO


-- 1. Using EXECUTE 
--Logged in as FRED
CREATE TABLE FRED.employee (
	-- other fields elided for clarity
	emp_id INT,
	name VARCHAR(20),
	address VARCHAR(50),
	phone VARCHAR(15),
	salary_grade INT,
	salary MONEY
)
GO

-- procedure for update
CREATE PROCEDURE FRED.update_salary(
	@EMP_ID INT,
	@NEW_SALARY MONEY)
AS
UPDATE FRED.employee SET salary = @NEW_SALARY
WHERE emp_id = @EMP_ID
GO

-- BOB can only execute the procedure
GRANT EXECUTE ON update_salary to BOB
GO

-- 2. Ownership chain and EXECUTE
-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE FRED.update_salary(
	@EMP_ID INT,
	@NEW_SALARY MONEY)
AS
-- FRED owns the employee table
UPDATE FRED.employee SET salary = @NEW_SALARY
WHERE emp_id = @EMP_ID
-- But ALICE owns the salary_audit table
INSERT INTO ALICE.salary_audit values(@EMP_ID, @NEW_SALARY)
GO

-- 3. Dynamic SQL
-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE FRED.update_salary(
	@EMP_ID INT,
	@NEW_SALARY MONEY)
AS
-- FRED owns the employee table
-- table is accessed through dynamic SQL
EXECUTE ('UPDATE FRED.employee SET salary = @NEW_SALARY
WHERE emp_id = @EMP_ID')
go
