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'