Do I have access rights to the SQL database?

ElrondCT
ElrondCT used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.)
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.
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
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.
Not the answer that I was hoping for, but still answered the question...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial