Retrieving a list of databases from SQL Server

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 ;)