Menu
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).
892
Answer: In SQL Server, there is a system view called sys. database_principals. You can run a query against this system view that returns all of the Users that have been created in SQL Server as well as information about these Users.
Using SQL Server Management Studio Next, under server, expand the “Security” directory. Now, under Security, expand the “Logins” option. This will display a list that contains all the logins created in the particular instance.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
