CODEWITHSUNDEEP
sql-server
DanielR
DanielR

Reputation: 724

Get list of databases user has access to

I have a SQL Server 2008 instance with several databases and I'm currently writing a C# application to access those databases. In this app, the end user can select a database they want to connect to.

I already have a list of all databases on the server, how can I limit that list to those databases the user can log in to? Or, how can I query that list?

There's a lot of databases, but each user can only access some of them, so trying to connect and catching the Exception is probably not a good idea.

Fyi: The server is configured for Windows authentication only, and the logins to the server are created for Windows' user groups (not individual users).

Upvotes: 13

Views: 28573

Answers (3)

Dexter Grace
Dexter Grace

Reputation: 21

You can use the system stored procedure sp_helplogins 'User Name'

Upvotes: 0

BIDeveloper
BIDeveloper

Reputation: 2638

Maybe as an alternative to Andomars answer (which I like!) you could interrogate Active Directory to see if the user is a member of a valid group for your database. I suspect this would mean you would have to maintain some Windows Group to Database Name lookup.

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You can query all databases from sys.sysdatabases, and check if the user has access with HAS_DBACCESS:

SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1

Upvotes: 30

Related Questions