check if database exists AND current login can acc

问题:

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

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

or...

SELECT DB_ID('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:

The server principal "login_name" is not able to access the database "database_name" under the current security context.

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

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

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.

回答1:

try:

Select HAS_DBACCESS('YourDatabaseNameHere')

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