获取用户有权访问的数据库列表 [英] Get list of databases user has access to

问题描述

我有一个包含多个数据库的 SQL Server 2008 实例，我目前正在编写一个 C# 应用程序来访问这些数据库.在此应用中，最终用户可以选择他们想要连接的数据库.

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.

仅供参考:服务器仅配置为 Windows 身份验证，服务器登录是为 Windows 用户组(而非个人用户)创建的.

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

推荐答案

您可以从 sys.sysdatabases 查询所有数据库，并通过 HAS_DBACCESS:

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

这篇关于获取用户有权访问的数据库列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！