--step 1
use MASTER
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'testDB')
DROP DATABASE testDB;
GO
IF EXISTS (SELECT * FROM SYSLOGINS WHERE NAME = 'testlogin1')
DROP LOGIN TestLogin1;
GO
CREATE DATABASE testDB;
GO
USE testDB
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.TestTable (id INT, col1 INT);
INSERT INTO TestSchema1.TestTable VALUES(42, 1);
--step 2
CREATE LOGIN TestLogin1 WITH PASSWORD='sqlpassword', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
GRANT SELECT ON SCHEMA::TestSchema1 TO TestUser1;
GO
CREATE OR ALTER PROC StoredProcThatWrites
AS
BEGIN
UPDATE TestSchema1.TestTable
SET col1 = '2' WHERE id > 0;
END;
GO
--step 3
GRANT EXEC ON StoredProcThatWrites TO testuser1;
GO
EXECUTE AS USER = 'testuser1';
GO
EXEC StoredProcThatWrites;
GO
UPDATE TestSchema1.TestTable
SET col1 = '2' WHERE id > 0;
GO
REVERT;
GO
--step 4
SELECT p.name, p.principal_id, s.name, USER_NAME(p.principal_id)
FROM sys.procedures AS p
JOIN sys.schemas AS s
ON p.schema_id = S.schema_id
WHERE p.name = 'StoredProcThatWrites';
GO
SELECT name, principal_id, USER_NAME(principal_id)
FROM sys.schemas
WHERE name = 'TestSchema1';
SELECT name, principal_id, USER_NAME(principal_id)
FROM sys.schemas
WHERE name = 'dbo';
--step 5
--should be error in stored procedure, but no error because of ownership chaining
--now break the chain, should now also get error in stored procedure
ALTER AUTHORIZATION ON StoredProcThatWrites TO testuser1;
EXECUTE AS USER = 'testuser1';
GO
EXEC StoredProcThatWrites;
GO
UPDATE TestSchema1.TestTable
SET col1 = '2' WHERE id > 0;
GO
REVERT;
GO
--with thanks and acknowledgement to https://www.mssqltips.com/sqlservertip/6394/understanding-sql-server-ownership-chaining/
--Housekeeping
use MASTER
ALTER DATABASE testDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE testDB;
GO
IF EXISTS (SELECT * FROM SYSLOGINS WHERE NAME = 'testlogin1')
DROP LOGIN TestLogin1;
GO
