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
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?
select name from master..sysdatabases where name <> 'master' and name <>'tempdb' and name<>'model' and name<>'msdb' 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
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
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.
ASKER
Not the answer that I was hoping for, but still answered the question...
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.)