troubleshooting Question

Do I have access rights to the SQL database?

Avatar of ElrondCT
ElrondCTFlag for United States of America asked on
Microsoft SQL ServerVisual Basic.NET
6 Comments1 Solution77 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
rspahitz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 6 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 6 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004