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;
DROP PROC StoredProcThatWrites;
GO
CREATE 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
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';
EXECUTE AS USER = 'testuser1';
GO
EXEC StoredProcThatWrites;
GO
UPDATE TestSchema1.TestTable
SET col1 = '2' WHERE id > 0;
GO
REVERT;
GO
