Rob Allen's DevNotes

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I need to get a list of databases from SQL Server for the currently logged in user. Initially, I found a built in function sp_databases. This looked promising, until I discovered that it didn't work for my user. I'm pretty sure that it's related to permissions. The user just has db_owner on a specific database and nothing else. (Mainly as I don't know how to set anything else!)

A bit of googling provided enabled me to develop this SQL statement:

SELECT name, collation_name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

This works, but gives me a list of all (non-system) databases, but doesn't tell me which ones I have permission to actually access.

I'm sure there's a better way, but I discovered that if I try to use a database, then I get an error message back, so a simple foreach loop solved my problem:

$validDatabases = array();
foreach($databases as $name) {
    $sql = "USE [$name]";
    try {
        $this->_db->query($sql);
    } catch (Exception $e) {
        // failed to access the database - ignore it
        continue;
    }
    $validDatabases[] = $name;
}

Now... this is inelegant. Surely there's a better way?!

On the plus side, it works ;)

This entry was posted on Tuesday, 19th May 2009 at 19:50 and is filed under WinPHP Challenge. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.