temp

 --Step 1 

use MASTER

IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')
CREATE DATABASE SoftwareSmithBufferPoolExtensions;
GO
USE SoftwareSmithBufferPoolExtensions
--drop table testtable1
 
CREATE TABLE TestTable1
(col1 INT IDENTITY PRIMARY KEY, 
col2 INT,
col3 nvarchar (4000),
col4 nvarchar (4000),
col5 nvarchar (4000),
col6 nvarchar (4000),
col7 nvarchar (4000),
col8 nvarchar (4000),
col9 nvarchar (4000),
col10 nvarchar (4000)
 ) 
GO 
SET NOCOUNT ON
DECLARE @i int = 0
WHILE @i < 10000000
BEGIN
    SET @i = @i + 1
INSERT INTO TestTable1 (col2, col3, col4, col5, col6, col7, col8, col9, col10)
VALUES (RAND() * 2147483647,
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647)
)
END
 
CHECKPOINT -- writes dirty pages to disk, cleans the buffers
DBCC DROPCLEANBUFFERS -- removes all buffers
 
--Step 2 
 
 --check MEMORYCLERK_SQLBUFFERPOOL allocation
SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) / 1024 DESC

SET STATISTICS IO ON
SELECT * FROM TestTable1 where col3 != col10

DBCC CHECKDB ('SoftwareSmithBufferPoolExtensions') WITH PHYSICAL_ONLY;

-- Housekeeping 
use master

IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')

DROP DATABASE SoftwareSmithBufferPoolExtensions;
go

SQL 2025 master class LAB ownership

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