CODEWITHSUNDEEP
sql-server
Glazius
Glazius

Reputation: 739

Can I safely confirm I have no access to a database?

Background

I maintain an application which spans multiple databases on a Microsoft SQL Server, let's call them PROD and ARCHIVE. For audit purposes, when a user accesses the databases through the application, their credentials are used on the database.

In some installations of this application, ARCHIVE doesn't exist, and code is in place to handle it not being present in sys.databases. But when ARCHIVE exists, everyone has access to both PROD and ARCHIVE.

Problem

We'd like to be able to fragment the application such that all users retain access to PROD, but some users aren't able to access ARCHIVE. In those cases I'd like to leverage the branching we have to check both whether ARCHIVE exists and whether the current login has any access to it.

Not that bad, except that I have to be able to run the query that finds it out with only access to PROD, and most of what I can find online is giving me queries to run against ARCHIVE.sys.database_permissions.

Is there a query I can run to verify that I cannot access ARCHIVE when I only have access to PROD?

Upvotes: 1

Views: 143

Answers (1)

Glazius
Glazius

Reputation: 739

HAS_DBACCESS

A little more searching got me to this:

HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid.

(via MSDN)

So what I needed in this case ended up being pretty simple:

select cast (ISNULL(HAS_DBACCESS('ARCHIVE'), 0) as bit);

which runs just fine on PROD. I'm not 100% sure on this because it'll also return false if the database has been taken offline, which is something I might actually want user-end visibility on, but that's something later development will need to deal with.

Upvotes: 2

Related Questions