Get list of databases user has access to

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

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

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.

You can use the system stored procedure sp_helplogins 'User Name'