Do I have access rights to the SQL database?

In Visual Basic 2008, my application is connecting to one of several possible (identically structured) SQL Server databases on a server. The user of the application may have permission to use only some of the databases. What's the most efficient way to determine whether the user can access a particular database? Currently, I'm simply enumerating all the databases on the server
select name from master..sysdatabases where  name <> 'master' and name <>'tempdb' and name<>'model' and name<>'msdb' 

Open in new window

then trying a SELECT on one of the tables in each database. If I don't have permission to use the database, I get an exception and move on to the next.

But it's well known that exceptions are very slow. That means that the enumeration of available databases is slow, as well. It seems to me there ought to be a better method to check permissions. Suggestions?
LVL 20
ElrondCTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
Depending on how complicated you want to get, you may be better off having a "master" user list: a place where all users have access, then get a list of the users and what DBs they have access to.
Where this gets tricky is that a mechanism should be added to automatically update this master table whenever a user permission changes on any of the systems (or at least some tool that can be run regularly to update the master list, maybe nightly or weekly depending on how often permissions change.)
ElrondCTAuthor Commented:
No, I don't want to get complicated. What I have currently works; I'm just looking for something, if it exists, to make it work faster. I'm thinking something like a way to query the database to ask if the user has appropriate roles (db_datareader and db_datawriter); I would think that would be faster than processing an exception. However, trying to open a SQL connection so I can do SELECT IS_MEMBER('db_datawriter') gives the same exception that I get when trying to load a table from INFORMATION_SCHEMA.TABLES ("The server principal "ZZZ" is not able to access the database "DDD" under the current security context.") I was hoping to find something that would send an error back without using exception handling, because that has a lot of overhead.
rspahitzCommented:
I'm not an expert on DB administration, but it seems that if the user does not have permissions to access the DB, it will return an exception.  Otherwise you'd have to open access to a specific connection that lets the user query something that would indicate if the user has permission...then you'd have ot lock everything else down...which seems like more of a security risk to me.

How do you manage users per DB now? If it's a central location, hooking into it to build a tiny DB regarding which DBs are accessible should be a relatively simple task.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Each database has a table named sys.database_principals where you can query and get the users with permissions. Problem is if you don't have access to the database you can't query it so I'll keep the suggestion to work the exception thrown by the system.
ElrondCTAuthor Commented:
rspahitz, I'm currently managing users by simply adding their login to the individual database with whichever roles are appropriate (db_datareader for read-only access, plus db_datawriter for read/write access, plus db_securityadmin for a superuser who can enable new users, and db_owner for a user, if any, who can authorize updates to the database structure when new versions of the software are released).

There's a limited number of databases that people use, and I have a "most recently used" list on the File menu, so it's probably not very frequent that my users need to browse for a database. Given what you're both saying, it's probably not worth trying to make a change. If I get complaints from users (I haven't had any up to now), then I can consider rspahitz's suggestion of a table of permissible databases.
ElrondCTAuthor Commented:
Not the answer that I was hoping for, but still answered the question...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.