Get list of databases user has access to

Question

I have a SQL Server 2008 instance with several databases and I'm currently writing a C# application to access those databases. In this app, the end user can select a database they want to connect to.

I already have a list of all databases on the server, how can I limit that list to those databases the user can log in to? Or, how can I query that list?

There's a lot of databases, but each user can only access some of them, so trying to connect and catching the Exception is probably not a good idea.

Fyi: The server is configured for Windows authentication only, and the logins to the server are created for Windows' user groups (not individual users).

Answer 1

You can query all databases from sys.sysdatabases, and check if the user has access with HAS_DBACCESS:

SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1

Answer 2

Maybe as an alternative to Andomars answer (which I like!) you could interrogate Active Directory to see if the user is a member of a valid group for your database. I suspect this would mean you would have to maintain some Windows Group to Database Name lookup.