**********************************************************************
Chapter 8 – Referenced syntax
**********************************************************************

SQL Server Forensic Analysis (SSFA)
by Kevvie Fowler 
ISBN: 0321544366
Copyright Pearson Education Inc. 2008


SSFA is an interactive book and if you are following along with chapter examples you can copy and paste the syntax found within this file to your local SQL Server instance. This will simplify your navigation of chapter examples.
---------


Page 227

CREATE DATABASE [Inv_307_Analysis] ON PRIMARY
( NAME = N'Analysis', FILENAME = N'path_to_data_file\Analysis.mdf' , SIZE = 51200KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Analysis_log', FILENAME = N'path_to_log_file\Analysis_log.ldf' , SIZE =
51200KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO

Page 233

BULK INSERT 'database_name'. 'owner'.’table’
FROM 'data_file'
WITH
(
FIELDTERMINATOR = 'field_terminator',
FIRSTROW = first_row,
ROWTERMINATOR = 'row_terminator',
CODEPAGE = code_page,
MAXERRORS = max_errors,
)

Page 234

BULK INSERT [AVLF_TLOG_MAN]
FROM 'Z:\TransactionLog.prep.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 3,
MAXERRORS = 0
)

Page 236

SELECT distinct cd.Collation as 'Victim System Collation Summary',
COLLATIONPROPERTY(COLLATION, 'CodePage') AS 'Code Page', fhc.Description from cldt_data
cd,::fn_helpcollations() fhc where cd.collation = fhc.name

Page 237

SELECT distinct cd.Collation as 'Analyst System Collation Summary',
COLLATIONPROPERTY(COLLATION, 'CodePage') AS 'Code Page', fhc.Description from
sys.syscolumns cd, ::fn_helpcollations() fhc where cd.collation = fhc.name

Page 239

CREATE DATABASE [Inv_307_Victim]
ON
(FILENAME = N'Z:\SSFA.mdf'),
(FILENAME = N'Z:\SSFA_log.ldf')
FOR ATTACH
ALTER DATABASE [Inv_307_Victim] SET READ_ONLY WITH NO_WAIT

Page 248

USE Inv_307_Analysis
SELECT dateadd (ms, rbf.[timestamp] - tme.ms_ticks, tme.date_time) as Time_Stamp,
cast(record as xml).value('(//SPID)[1]', 'bigint') as SPID,
cast(record as xml).value('(//APIName)[1]', 'varchar(255)') as Failed_API,
cast(record as xml).value('(//CallingAPIName)[1]', 'varchar(255)') as
Calling_API,
cast(record as xml).value('(//ErrorCode)[1]', 'varchar(255)') as Error_Code,
cast(record as xml) Original_Record
from RBUF_Data rbf
cross join SYST_Time tme
where rbf.ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'

Page 254

USE INV_307_ANALYSIS
DECLARE @LOGIN VARCHAR (100)
DECLARE @DBNAME VARCHAR (100)
SET @DBNAME = 'SSFA'
SET @LOGIN = 'EASYACCESS'
-- ENDPOINTS
SELECT '1) Server | Endpoint' as 'category', ssr.name as 'grantee', ssp.state_desc as
'assignment', ssp.permission_name, CASE WHEN ssp.class = 105 THEN RTRIM(ssp.class_desc)
+ '.' + CONVERT (VARCHAR, ssp.major_id) ELSE ssp.class_desc END as 'object',
ssp.class_desc as 'object_type', ssi.name as 'grantor' FROM SERV_Perm ssp, SERV_Prin
ssr, SERV_Prin ssi WHERE ssp.grantee_principal_id = ssr.principal_id and
ssp.grantor_principal_id = ssi.principal_id and ssp.class = 105 and rtrim(ssr.name) IN
(@LOGIN, 'PUBLIC')
-- LOGINS
UNION ALL SELECT '2) Server | Login', name, 'N/A', CASE WHEN logn_sql.status = 1 THEN
'ENABLED' ELSE 'DISABLED' END, 'N/A', 'N/A', 'N/A' from logn_sql WHERE
rtrim(logn_sql.name) IN (@LOGIN, 'PUBLIC')
--Fixed Server Role Membership
UNION ALL SELECT '3) Server | Fixed-server role', syu.name, 'N/A', sys.name , 'N/A',
'N/A', 'N/A' from SERV_Prin syu, SERV_Prin sys, SERV_Rmem sym where
sym.member_principal_id = syu.[principal_id] and sym.role_principal_id =
sys.[principal_id] and syu.name IN (@LOGIN, 'PUBLIC')
-- Database
UNION ALL SELECT '4) Database | User', sdr.name, CONVERT(VARCHAR, sde.state_desc),
CONVERT(VARCHAR, sde.permission_name), sde.class_desc, sde.class_desc, sdi.name from
DBSE_Prin sdr, DBSE_Prin sdi, DBSE_Perm sde where sde.grantee_principal_id =
sdr.principal_id and sde.grantor_principal_id = sdi.principal_id and class = 0 and
sdr.name IN (@LOGIN, 'PUBLIC')
--Database Role Membership
UNION ALL SELECT '5) Database | Fixed/User-defined role', syu.name, 'N/A', sys.name ,'N/A', 'N/A', 'N/A' from DBSE_Prin syu, DBSE_Prin sys, DBSE_Rmem sym where
sym.member_principal_id = syu.[principal_id] and sym.role_principal_id =
sys.[principal_id] and syu.sid IN ((select sid from LOGN_SQL where name = @LOGIN),
(select sid from LOGN_SQL where name = 'PUBLIC'))
-- Schema
UNION ALL SELECT '6) Schema | Schema', sdi.name as 'grantee', sde.state_desc,
sde.permission_name, sao.name, sde.class_desc, sdr.name as 'grantor' from DBSE_Perm
sde, DBSE_Prin sdi, DBSE_Prin sdr, SCHM_Data sao where sdi.principal_id =
sde.grantee_principal_id and sdr.principal_id = sde.grantor_principal_id and
sao.schema_id = sde.major_id and sde.class = 3 and sao.[database] = @DBNAME and
sdi.sid = (select sid from LOGN_SQL where name = @LOGIN)
--Database Object
UNION ALL SELECT '7) Schema | Object', dbr.name, dbe.state_desc, dbe.permission_name,
RTRIM(scd.name) + '.' + CASE WHEN dbe.minor_id >0 THEN RTRIM(syo.name) + '.' + (select
name from CLDT_Data where ID = syo.object_id and syo.[database] = @DBNAME and colid =
dbe.minor_id) ELSE syo.name END as 'Object', CASE WHEN dbe.minor_id >0 THEN
RTRIM(syo.type_desc) + '_COLUMN' ELSE RTRIM(syo.type_desc) END as 'type', dbi.name from
DOBJ_Data syo,SCHM_Data scd, DBSE_Prin dbr, DBSE_Prin dbi, DBSE_Perm dbe where
dbr.principal_id = dbe.grantee_principal_id and dbi.principal_id =
dbe.grantor_principal_id and syo.[database] = @DBNAME and scd.[database] = @DBNAME and
scd.schema_id = syo.schema_id and dbr.sid = (select sid from LOGN_SQL where name =
@LOGIN) and dbe.major_id = syo.object_id order by category, grantee, object

Page 266

USE Inv_307_Analysis
SELECT [name], symmetric_key_id, create_date, modify_date FROM DBSE_SEMK WHERE KEY_GUID
= CAST(cipher_text AS uniqueidentifier)

Page 267

--Code block 1

SELECT [name], symmetric_key_id, create_date, modify_date FROM DBSE_SEMK WHERE KEY_GUID=
CAST(0x005FC9BB28EF164B84CF2B67A9C4FFFB010000009CB00B1E7B83C991531245F0703F77B4E7E7F16
60CE535EE7213211449249221FAC4C1E9D17BD1EC5AB425A371948451 AS uniqueidentifier)

--Code block 2

USE SSFA
--Declare a variable which will store our cipher text
DECLARE @CTEXT varbinary(256)
--First open your earlier created encryption key to encrypt our test phrase
--Open symmetric encryption key for use
OPEN symmetric key SSFA_symkey
DECRYPTION BY ASYMMETRIC KEY SSFA_Asymkey WITH PASSWORD = 'J#yAsdl8z)f3tq'
--
--Encrypt string
SET @CTEXT = ENCRYPTBYKEY(KEY_GUID('SSFA_Symkey'), 'SQL Server Forensic Analysis')
--
--Identify the encryption key used to encrypt data
SELECT @CTEXT as 'ciphertext', CAST (@CTEXT AS uniqueidentifier) as 'signature', [name]
as 'key_name', symmetric_key_id, create_date, modify_date FROM sys.symmetric_keys WHERE
KEY_GUID= CAST(@CTEXT AS uniqueidentifier)


Page 269 

use inv_307_Analysis
SELECT syk.name as 'key_name', class_desc, spr.name as 'db_user', permission_name,
state_desc, major_id from dbse_perm sdp, dbse_semk syk, dbse_prin spr where class IN
(24, 25, 26) and syk.symmetric_key_id = sdp.major_id and spr.principal_id =
sdp.grantee_principal_id and class = 24
UNION ALL select syk.name as 'key_name', class_desc, spr.name as 'db_user',
permission_name, state_desc, major_id from dbse_perm sdp, dbse_asmk syk, dbse_prin spr
where syk.asymmetric_key_id = sdp.major_id and spr.principal_id =
sdp.grantee_principal_id and class = 26
UNION ALL select syk.name as 'key_name', class_desc, spr.name as 'db_user',
permission_name, state_desc, major_id from dbse_perm sdp, dbse_cert syk, dbse_prin spr
where class IN (24, 25, 26) and syk.certificate_id = sdp.major_id and spr.principal_id
= sdp.grantee_principal_id and class = 25
ORDER BY key_name, spr.name, permission_name, state_desc