**********************************************************************
Chapter 11 – 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 400

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

Page 406

USE INV_308_Scenario
DECLARE @LOGIN VARCHAR (100)
DECLARE @DBNAME VARCHAR (100)
SET @DBNAME = 'ONLINESALES'
SET @LOGIN = 'MSmith'
-- 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 or name = 'PUBLIC')
--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 or name = 'PUBLIC') and dbe.major_id = syo.object_id order by category, grantee,
object

Page 409

select dbj.name as 'object', dbj.type_desc as 'object_type', cdt.name as 'column_name',
st.name as 'datatype' FROM CLDT_Data cdt, systypes st, DOBJ_Data dbj where st.xusertype
= cdt.xusertype and dbj.object_id = cdt.id and st.name = 'Varbinary' and
dbj.is_ms_shipped = 0

Page 410

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
ORDER BY key_name, spr.name, permission_name, state_desc

Page 412

SELECT * FROM PLCH_Data WHERE CAST ([Creation_time] AS DATETIME) >= cast ('2008-08-31
15:32:41.000' AS DATETIME) AND CAST ([Creation_time] AS DATETIME) <= CAST ('2008-08-31
15:32:41.999' AS DATETIME) or CAST ([Last_execution_time] AS DATETIME) >= cast ('2008-
08-31 15:32:41.000' AS DATETIME) AND CAST ([Last_execution_time] AS DATETIME) <= CAST
('2008-08-31 15:32:41.999' AS DATETIME) order by last_execution_time desc

Page 416

SELECT DISTINCT [TRANSACTION NAME], [BEGIN TIME], [current lsn] FROM AVLF_TLOG where
[TRANSACTION ID] IN (SELECT distinct [TRANSACTION ID] FROM AVLF_TLOG WHERE [TRANSACTION SID] = '0x501AEC871FD432488B4A487B06C61505') and [transaction name] <> 'NULL' order by
[current lsn]

Page 420

SELECT * FROM PLCH_Data WHERE CAST ([Creation_time] AS DATETIME) >= cast ('2008-08-31
15:45:15.000' AS DATETIME) AND CAST ([Creation_time] AS DATETIME) <= CAST ('2008-08-31
15:45:15.999' AS DATETIME) or CAST ([Last_execution_time] AS DATETIME) >= cast ('2008-
08-31 15:45:15.000' AS DATETIME) AND CAST ([Last_execution_time] AS DATETIME) <= CAST
('2008-08-31 15:45:15.999' AS DATETIME) order by last_execution_time desc

Page 421

SELECT * FROM LOGN_SQL WHERE CAST ([UPDATEDATE] AS DATETIME) >= cast ('2008/08/31
15:45:43:000' AS DATETIME) AND CAST ([UPDATEDATE] AS DATETIME) < CAST ('2008/08/31
15:45:43:999' AS DATETIME)