检查数据库是否存在并且当前登录可以访问 [英] check if database exists AND current login can access

问题描述

我知道我可以使用...检查数据库是否存在

I know I can check if a database exists using...

SELECT * FROM sys.databases WHERE name = 'database_name'

或...

SELECT DB_ID('database_name')

无论当前登录是否有权访问database_name，都可以执行此检查.例如，如果当前登录没有访问权限，则此语句...

This check can be performed regardless of whether the current login has access to database_name. If the current login does not have access, for instance, then this statement...

USE database_name

...可以理解为失败并显示此消息:

...will understandably fail with this message:

<代码>服务器主体login_name"在当前安全上下文下无法访问数据库database_name".

我想要的是一个查询，告诉我当前登录是否可以访问当前数据库.我试着用 try catch 包装...

What I want is a query to tell me if the current login can access the current database. I tried wrapping in a try catch...

begin try
    use database_name;
end try
begin catch
    select 'cannot access database_name'
end catch

...但这会产生与上面相同的错误，甚至没有抓住.这让我感到惊讶——我希望能取回自定义消息.也许你不能在 try catch 块中使用 USE 语句，但无论如何，try..catch 有点像黑客.

... but that yields the same error as above, without even getting to the catch. This was surprising to me -- I expected to get the custom message back. Maybe you cannot have USE statements inside try catch blocks, but in any event, the try..catch sort of seems like a hack.

那么是否有查询只返回当前登录可以访问的数据库?我想在 SSIS 包中使用它来确定数据库是否存在并且可以在继续之前访问.正如现在所写(使用 IF EXISTS...)，执行 SQL 任务返回 true，然后以下步骤失败.我宁愿执行 SQL 任务返回 false，甚至不执行以下步骤.

So is there a query to return only the databases that can be accessed by the current login? I want to use this within an SSIS package to determine if a database exists and can be accessed before continuing. As it is written now (using IF EXISTS...), the Execute SQL Task returns true, then the following step fails. I would much rather the Execute SQL Task returns false, and the following step is not even executed.

作为参考，我使用的登录名仅分配给公共"服务器角色.

For reference, the login I am using is only assigned to the 'public' server role.

推荐答案

尝试:

Select HAS_DBACCESS('YourDatabaseNameHere')

如果返回 1，则登录具有访问权限.0 表示无法访问.NULL 表示数据库不存在.

If this returns 1, the login has access. 0 means no access. NULL means the database does not exist.

这篇关于检查数据库是否存在并且当前登录可以访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！