In SQL Server, there are two kinds of tables are invisible. One is system protected tables, for instance, sys.sysrscols. You could access them by using Dedicated Administrator Connection (DAC) to connection to the server.
Enable DAC:exec sp_configure 'remote admin connections', 1Connect using DAC: specify ADMIN:ServerName in connection string, then you can run select * from sys.sysrscols to get result of the table. This type of tables can only be seen by admin connection.
reconfigure
Another type of tables is temporary tables, including local temporary table and table variable; they are only accessible by owner scope and there is no "official" way to access them out of scope. For instance, if a table variable is created in a function or procedure or a batch, it can only be accessed by them but not other sessions, and procedures and functions called by them, including dynamic SQL. If a local temporary table is created in a procedure or batch (you can't create local temporary table within a function), it cannot be accessed by non-owner session too, but they can be accessed by the procedure and function invoked by them, including dynamic SQL.
How can you access a temporary table/table variable created by other session and how can you access system protected table without connecting with DAC? The answer is directly access data page in the data page. By knowing the name of the table, we could find out the object id in sys.objects table and then use DBCC IND lists all index pages, then use DBCC PAGE to show what is on the page and then PIVOT it to the structure like the original table structure. Please see the code below.
use master
go
create procedure dbo.sp_ReadTable
(
@ObjectID int
)
as
begin
dbcc traceon(3604)
declare @SQL nvarchar(max)
create table #ind(id int identity(1,1) primary key, PageFID smallint, PagePID int, IAMFID smallint, IAMPID int, ObjectID int, IndexID int, PartitionNumber bigint, PartitionID bigint, iam_chain_type varchar(50), PageType int, IndexLevel int, NexPageFID int, NextPagePID int, PrevPageFID int, PrevPagePID int)
create table #page(id int identity(1,1) primary key,ParentObject varchar(300), Object varchar(300), Field varchar(300), Value varchar(6000))
create table #res(id int primary key, Page varchar(100), Slot varchar(300), Object varchar(300), FieldName varchar(300), Value varchar(6000))
create table #ColumnName(ColumnID int primary key, Name sysname)
insert into #ColumnName
select column_id, name
from sys.columns
where object_id = @ObjectID
select @SQL = 'dbcc ind(' + quotename(db_name())+', ' + cast(@ObjectID as varchar(20)) + ', '+cast(b.indid as varchar(20))+')'
from sysobjects a
inner join sysindexes b on a.id = b.id
where b.indid in (0,1) and a.id = @ObjectID
insert into #ind
exec sp_executesql @SQL
declare @PageFID smallint, @PagePID int
declare c cursor for
select top 3 PageFID, PagePID from #ind where PageType = 1
open c
fetch next from c into @PageFID, @PagePID
while @@fetch_status = 0
begin
delete #page
select @SQL = 'dbcc page (' + quotename(db_name()) + ',' + cast(@PageFID as varchar(20)) + ',' + cast(@PagePID as varchar(20)) + ', 3) with tableresults'
insert into #page
exec sp_executesql @SQL
delete from #page where Object not like 'Slot %' or Field = '' or Field in ('Record Type', 'Record Attributes') or ParentObject in ('PAGE HEADER:')
insert into #res
select id, cast(@PageFID as varchar(20)) + ':' + CAST(@PagePID as varchar(20)), ParentObject, Object, Field, Value from #page
fetch next from c into @PageFID, @PagePID
end
close c
deallocate c
--select *, @ObjectID ObjectID ,object_name(@ObjectID) ObjectName from #res order by id
select @SQL = '
select __HashedRecID__'+cast((select ',[' + Name + ']' from #ColumnName order by ColumnID for xml path(''), root('root')) as xml).value('/root[1]', 'nvarchar(max)')+'
from (select HASHBYTES(''SHA1'', slot) __HashedRecID__, FieldName, Value from #res) p
PIVOT
(
max(Value)
FOR FieldName IN
( ' + stuff(cast((select ',[' + Name + ']' from #ColumnName order by ColumnID for xml path(''), root('root')) as xml).value('/root[1]', 'nvarchar(max)'), 1,1,'') + ' )
) AS pvt
'
--print @SQL
exec(@SQL)
end
go
exec sp_MS_marksystemobject 'dbo.sp_ReadTable'
use tempdb
exec sp_ReadTable 5 --sysrowsets
Wow, that's cool. __HashRecID__ is an internal ID that I created for pivoting records generated by DBCC PAGE.
Now let's see if this procedure could help see records in the temporary table that created by other session.
First of all, start one window in Management Studio and create a temporary table
Let's see the result:
From this screen, we could tell, we are able to display the content of temporary table created in session 60 in session 58! By using the same way, you can also see the content of table variables created by any session. For instance, in one window, you can create a table variable and have an indefinite loop to insert one record per second. Open another window and change the database to tempdb then guess which line is the table variable is created by that session, like the screen shot above, you could not tell which table variable line number 4 is. And then pass the object id to sp_ReadTable procedure.
Here is the result:
In SQL Server, there is almost no secret. All tables can be accessed by DBCC PAGE. When a database is created with Transparent Database Encryption, the data in temporary table will be encrypted as well, nevertheless, those encrypted temporary table can also be read in this way as I mentioned in my first blog that encrypted data gets decrypted before entering buffer pool. DBCC PAGE is reading decrypted from buffer pool.
Posts
