Project Zebra: Minimalist customisation on old hardware ►
◄ A couple of IDEA regex functions and some simple code snippets
Just a quickie... these tips are equally applicable whether you're auditing or working to extract particular information from a database.
I've previously mentioned this script for reporting on server, database and object level permissions with SQL Server, and if you're trying to tease out what a compromised account exposes then these days with most installations (2008 onwards) you've got sys.fn_my_permissions at your disposal.
Similarly, everyone knows and relies on information_schema.columns as well as system designers tending to choose table names containing obvious hints (such as 'setting', 'config', 'user', 'audit', 'log', etc if you want to know how things were built, rather than what records are typically stored). It's worth remembering to have a look at views as well as tables, of course, and that there are still people who'll store connection strings or plaintext passwords in databases.
Counting records in every table is another way to home in on stored data of interest, even if it's just to confirm with disappointment that the audit logs don't record what you were hoping. There are various ways to do this and cycling through with sp_MSforeachtable is a nice shortcut;
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount] FROM @TableRowCounts ORDER BY [RowCount] DESC
GO
Sometimes you've got access to existing reports, such as Crystal Reports .rpt files, and whilst the underlying SQL is semi-readable if you open them in a text editor, it's preferable to open them in their intended application. But with Crystal Reports in particular, it looks for the database they were written for. How to get to the queries?
https://stackoverflow.com/questions/3248001/extract-raw-sql-query-from-a-crystal-report-rpt-file
Untick "Verify Database Every Time" if necessary (although I've never had to) and create a 'blank' valid ODBC connection, then choose "Show SQL" and give it the ODBC details you've just created. If you don't have a database to connect to, you could grab anything Microsoft have lying around as samples for Access.
