Extract DB Permission

Script to extract DB permissions

 

SET NOCOUNT ON
GO

SELECT 'Use ' + db_name()

PRINT 'go'
GO

SELECT 'EXEC sp_changedbowner ''' + suser_sname(sid) + ''''
FROM master..sysdatabases
WHERE name = db_name()
GO

SELECT '--  ' + name
	,'-- ' + filename
FROM sysfiles
GO

SELECT '--alter database  ' + db_name() + '  SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
GO

BEGIN
	DECLARE @dRoleName [sysname]

	DECLARE _outer CURSOR LOCAL FORWARD_ONLY READ_ONLY
	FOR
	SELECT 'RoleName' = name
	FROM sysusers
	WHERE (
			issqlrole = 1
			OR isapprole = 1
			)
		AND [name] NOT IN (
			'public'
			,'INFORMATION_SCHEMA'
			,'db_owner'
			,'db_accessadmin'
			,'db_securityadmin'
			,'db_ddladmin'
			,'db_backupoperator'
			,'db_datareader'
			,'db_datawriter'
			,'db_denydatareader'
			,'db_denydatawriter'
			)

	OPEN _outer

	FETCH NEXT
	FROM _outer
	INTO @dRoleName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @DatabaseRoleName [sysname]

		--SET @DatabaseRoleName = '{Database Role Name}'
		SET @DatabaseRoleName = @dRoleName
		SET NOCOUNT ON

		DECLARE @errStatement [varchar] (8000)
			,@msgStatement [varchar] (8000)
			,@DatabaseRoleID [smallint]
			,@IsApplicationRole [bit]
			,@ObjectID [int]
			,@ObjectName [sysname]

		SELECT @DatabaseRoleID = [uid]
			,@IsApplicationRole = CAST([isapprole] AS BIT)
		FROM [dbo].[sysusers]
		WHERE [name] = @DatabaseRoleName
			AND (
				[issqlrole] = 1
				OR [isapprole] = 1
				)
			AND [name] NOT IN (
				'public'
				,'INFORMATION_SCHEMA'
				,'db_owner'
				,'db_accessadmin'
				,'db_securityadmin'
				,'db_ddladmin'
				,'db_backupoperator'
				,'db_datareader'
				,'db_datawriter'
				,'db_denydatareader'
				,'db_denydatawriter'
				)

		IF @DatabaseRoleID IS NULL
		BEGIN
			IF @DatabaseRoleName IN (
					'public'
					,'INFORMATION_SCHEMA'
					,'db_owner'
					,'db_accessadmin'
					,'db_securityadmin'
					,'db_ddladmin'
					,'db_backupoperator'
					,'db_datareader'
					,'db_datawriter'
					,'db_denydatareader'
					,'db_denydatawriter'
					)
				SET @errStatement = 'Role ' + @DatabaseRoleName + ' is a fixed database role and cannot be scripted.'
			ELSE
				SET @errStatement = 'Role ' + @DatabaseRoleName + ' does not exist in ' + DB_NAME() + '.' + CHAR(13) + 'Please provide the name of a current role in ' + DB_NAME() + ' you wish to script.'

			RAISERROR (
					@errStatement
					,16
					,1
					)
		END
		ELSE
		BEGIN
			SET @msgStatement = '--Security creation script for role ' + @DatabaseRoleName + CHAR(13) + '--Created At: ' + CONVERT(VARCHAR, GETDATE(), 112) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + CHAR(13) + '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add Role To Database' + CHAR(13)

			IF @IsApplicationRole = 1
				SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) + CHAR(9) + '@rolename = ''' + @DatabaseRoleName + '''' + CHAR(13) + CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13)
			ELSE
			BEGIN
				SET @msgStatement = ''
				SET @msgStatement = @msgStatement + 'EXEC sp_addrole ' + '@rolename =''' + @DatabaseRoleName + '''
go'
			END

			SET @msgStatement = @msgStatement

			PRINT @msgStatement

			DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY
			FOR
			SELECT DISTINCT ([sysobjects].[id])
				,'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
			FROM [dbo].[sysprotects]
			INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
			WHERE [sysprotects].[uid] = @DatabaseRoleID

			OPEN _sysobjects

			FETCH NEXT
			FROM _sysobjects
			INTO @ObjectID
				,@ObjectName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @msgStatement = ''

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 193
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'SELECT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 195
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'INSERT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 197
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'UPDATE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 196
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'DELETE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 224
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'EXECUTE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 26
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'REFERENCES,'

				IF LEN(@msgStatement) > 0
				BEGIN
					IF RIGHT(@msgStatement, 1) = ','
						SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
					SET @msgStatement = 'GRANT ' + @msgStatement + ' ON ' + @ObjectName + ' TO ' + @DatabaseRoleName + '
go'

					PRINT @msgStatement
				END

				SET @msgStatement = ''

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 193
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'SELECT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 195
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'INSERT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 197
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'UPDATE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 196
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'DELETE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 224
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'EXECUTE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseRoleID
							AND [action] = 26
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'REFERENCES,'

				IF LEN(@msgStatement) > 0
				BEGIN
					IF RIGHT(@msgStatement, 1) = ','
						SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
					SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName

					PRINT @msgStatement
				END

				FETCH NEXT
				FROM _sysobjects
				INTO @ObjectID
					,@ObjectName
			END

			CLOSE _sysobjects

			DEALLOCATE _sysobjects
				--PRINT 'GO'
		END

		FETCH NEXT
		FROM _outer
		INTO @dRoleName
	END
END

--------------For users
BEGIN
	DECLARE @DatabaseUserName [sysname]
	DECLARE @UName SYSNAME

	SET NOCOUNT ON

	DECLARE
		--@errStatement [varchar](8000),
		--@msgStatement [varchar](8000),
		@DatabaseUserID [smallint]
		,@ServerUserName [sysname]
		,@RoleName [varchar] (8000)

	--@ObjectID [int],
	--@ObjectName [varchar](261)
	DECLARE _dbusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
	FOR
	SELECT [sysusers].name
	FROM [dbo].[sysusers]
	INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
	WHERE [sysusers].name <> 'dbo'
		AND [sysusers].name <> 'dbo'

	OPEN _dbusers

	FETCH NEXT
	FROM _dbusers
	INTO @UName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		--cursor ends for all users
		SET @DatabaseUserName = @UName

		SELECT @DatabaseUserID = [sysusers].[uid]
			,@ServerUserName = [master].[dbo].[syslogins].[loginname]
		FROM [dbo].[sysusers]
		INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
		WHERE [sysusers].[name] = @DatabaseUserName

		IF @DatabaseUserID IS NULL
		BEGIN
			SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'

			RAISERROR (
					@errStatement
					,16
					,1
					)
		END
		ELSE
		BEGIN
			SET @msgStatement = ''
			SET @msgStatement =
				--'--Add User To Database' + CHAR(13) +
				'EXEC [sp_grantdbaccess]' + ' @loginame =''' + @ServerUserName + ''',' + ' @name_in_db =''' + @DatabaseUserName + '''
GO'

			PRINT @msgStatement

			DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
			FOR
			SELECT [name]
			FROM [dbo].[sysusers]
			WHERE
				--name not like 'db_owner' and 
				[uid] IN (
					SELECT [groupuid]
					FROM [dbo].[sysmembers]
					WHERE [memberuid] = @DatabaseUserID
					)

			OPEN _sysusers

			FETCH NEXT
			FROM _sysusers
			INTO @RoleName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @msgStatement = 'EXEC [sp_addrolemember] ' + '@rolename = ''' + @RoleName + ''',' + ' @membername = ''' + @DatabaseUserName + '''
go'

				PRINT @msgStatement

				FETCH NEXT
				FROM _sysusers
				INTO @RoleName
			END

			SET @msgStatement = '' + CHAR(13)

			PRINT @msgStatement

			DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY
			FOR
			SELECT DISTINCT ([sysobjects].[id])
				,'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
			FROM [dbo].[sysprotects]
			INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
			WHERE [sysprotects].[uid] = @DatabaseUserID

			OPEN _sysobjects

			FETCH NEXT
			FROM _sysobjects
			INTO @ObjectID
				,@ObjectName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @msgStatement = ''

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 193
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'SELECT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 195
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'INSERT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 197
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'UPDATE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 196
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'DELETE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 224
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'EXECUTE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 26
							AND [protecttype] = 205
						)
					SET @msgStatement = @msgStatement + 'REFERENCES,'

				IF LEN(@msgStatement) > 0
				BEGIN
					IF RIGHT(@msgStatement, 1) = ','
						SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
					SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName

					PRINT @msgStatement
				END

				SET @msgStatement = ''

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 193
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'SELECT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 195
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'INSERT,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 197
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'UPDATE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 196
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'DELETE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 224
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'EXECUTE,'

				IF EXISTS (
						SELECT *
						FROM [dbo].[sysprotects]
						WHERE [id] = @ObjectID
							AND [uid] = @DatabaseUserID
							AND [action] = 26
							AND [protecttype] = 206
						)
					SET @msgStatement = @msgStatement + 'REFERENCES,'

				IF LEN(@msgStatement) > 0
				BEGIN
					IF RIGHT(@msgStatement, 1) = ','
						SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
					SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName

					PRINT @msgStatement
				END

				FETCH NEXT
				FROM _sysobjects
				INTO @ObjectID
					,@ObjectName
			END

			CLOSE _sysobjects

			DEALLOCATE _sysobjects
		END

		CLOSE _sysusers

		DEALLOCATE _sysusers

		FETCH NEXT
		FROM _dbusers
		INTO @UName
	END

	CLOSE _dbusers

	DEALLOCATE _dbusers
		--end
		--END
END

--sp_helptext sp_helprolemember
---------------------------------Generating script to add role members --------------------------------
SET NOCOUNT ON

IF object_id('tempdb..#t') IS NOT NULL
	DROP TABLE #t

SELECT DbRole = g.name
	,MemberName = u.name
	,MemberSID = u.sid
INTO #t
FROM sysusers u
	,sysusers g
	,sysmembers m
WHERE g.uid = m.groupuid
	AND g.issqlrole = 1
	AND u.uid = m.memberuid
	AND 1 = 2

INSERT INTO #t
EXEC sp_helprolemember

DECLARE @dbrole VARCHAR(800)
DECLARE @membername VARCHAR(800)

DECLARE _addrole CURSOR
FOR
SELECT DbRole
	,MemberName
FROM #t

OPEN _addrole

FETCH NEXT
FROM _addrole
INTO @dbrole
	,@membername

WHILE @@FETCH_STATUS = 0
BEGIN
	--print @dbrole +' '+ @membername
	IF @membername <> 'dbo'
		PRINT 'sp_addrolemember @rolename =''' + @dbrole + ''',  @membername = ''' + @membername + '''
go'

	FETCH NEXT
	FROM _addrole
	INTO @dbrole
		,@membername
END

CLOSE _addrole

DEALLOCATE _addrole
	/*
RESTORE FILELISTONLY from disk = 'C:\Temp\<DB_Name>.bak' 

restore database <DB_Name>
from disk = 'C:\Temp\<DB_Name>.bak' 
with move 'LogicalName_data' to 'D:\DBName_Data.mdf',
move 'Logicalname_log' to 'L:\DBName_Log.ldf', 
stats =10, 
replace
*/

Query Progress

Putting together various scripts developed by people in the community to check the progress of currently running queries. Some of them I have tweaked to make them more readable.

Script 1:  Time remaining for DB Backup & Restore

 

--https://www.sqlservercentral.com/blogs/how-to-get-estimated-completion-time-of-sql-server-database-backup-or-restore
SELECT dmr.session_id
	,dmr.command
	,CONVERT(NUMERIC(6, 2), dmr.percent_complete) AS [Percent Complete]
	,CONVERT(VARCHAR(20), DATEADD(ms, dmr.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
	,CONVERT(NUMERIC(10, 2), dmr.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
	,CONVERT(VARCHAR(1000), (
			SELECT SUBSTRING(TEXT, dmr.statement_start_offset / 2, CASE 
						WHEN dmr.statement_end_offset = - 1
							THEN 1000
						ELSE (dmr.statement_end_offset - dmr.statement_start_offset) / 2
						END)
			FROM sys.dm_exec_sql_text(sql_handle)
			)) [sqltxt]
FROM sys.dm_exec_requests dmr
WHERE command IN (
		'RESTORE DATABASE'
		,'BACKUP DATABASE'
		,'BACKUP LOG'
		,'RESTORE LOG'
		)

Script 2:  Time remaining for below commands :

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
ROLLBACK
TDE ENCRYPTION

SELECT session_id
	,percent_complete
	,start_time
	,STATUS
	,DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP) Estimated_finish_time
	,(total_elapsed_time / 1000) / 60 Total_Elapsed_Time_MINS
	,DB_NAME(Database_id) Database_Name
	,command
	,last_wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50
	AND session_id <> @@spid
	--and DB_NAME(Database_id)='AWSDB'
	--and session_id=652

Script 3:  Progress of CREATE INDEX

--https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
DECLARE @SPID INT = 51;;

WITH agg
AS (
	SELECT SUM(qp.[row_count]) AS [RowsProcessed]
		,SUM(qp.[estimate_row_count]) AS [TotalRows]
		,MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS]
		,MAX(IIF(qp.[close_time] = 0
				AND qp.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [CurrentStep]
	FROM sys.dm_exec_query_profiles qp
	WHERE qp.[physical_operator_name] IN (
			N'Table Scan'
			,N'Clustered Index Scan'
			,N'Index Scan'
			,N'Sort'
			)
		AND qp.[session_id] = @SPID
	)
	,comp
AS (
	SELECT *
		,([TotalRows] - [RowsProcessed]) AS [RowsLeft]
		,([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
	FROM agg
	)
SELECT [CurrentStep]
	,[TotalRows]
	,[RowsProcessed]
	,[RowsLeft]
	,CONVERT(DECIMAL(5, 2), (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
	,[ElapsedSeconds]
	,(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft]
	,DATEADD(SECOND, (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]), GETDATE()) AS [EstimatedCompletionTime]
FROM comp;

Script 4:  Progress of SELECT INTO

 

--https://dba.stackexchange.com/questions/129090/progress-of-select-into-statement/129152#129152
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
	SUM(sdobd.[row_count]) AS [BufferPoolRows]
	,SUM(sp.[rows]) AS [AllocatedRows]
	,COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN sys.allocation_units sau ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN sys.partitions sp ON (
		sau.[type] = 1
		AND sau.[container_id] = sp.[partition_id]
		) -- IN_ROW_DATA
	OR (
		sau.[type] = 2
		AND sau.[container_id] = sp.[hobt_id]
		) -- LOB_DATA
	OR (
		sau.[type] = 3
		AND sau.[container_id] = sp.[partition_id]
		) -- ROW_OVERFLOW_DATA
WHERE sdobd.[database_id] = DB_ID()
	AND sdobd.[page_type] = N'DATA_PAGE'
	AND sp.[object_id] = (
		SELECT so.[object_id]
		FROM sys.objects so
		WHERE so.[name] = 'TestDump'

Done!! I hope this helps 😊