Allowing application to browse and filter databases

I have an application written in C# which queries a SQL Server database (2008 or 2008 Express in most instances) and have recently had to implement SQL Server authentication for smaller businesses where they do not have a domain available for Windows Authentication.

As a result of this I have to set up Logins for each machine and allow them to read and write for normal access which is all fine. The problem comes when I try to get the list of databases that they are able to use with the application, which requires sysadmin permissions.

How can I get the list of databases, iterate them and return the list of valid names without having to give every login sysadmin permissions?

The above is only very mildly cut down from the full code. It works perfectly if the user has SysAdmin permissions, and I want to either provide different permissions that will tighten it up or use an alternative method that does not need raised permissions for client applications.

Because the databases will usually be set up by local DBAs I want to do this using SQL Server Management Studio if at all possible - this sort of power should not be delegated to the client application even with the right credentials!

I tried your suggestion assigning login to master and model since those are the ones that would be needed, but unfortunately no go, still the same issue. In each case I assigned db_datareader database role membership, should I have done something else?

Zberteoc:

That won't allow access to the databases to determine if they contain the required table that identifies the database as belonging to the application?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

System databases do not include a user database.
Where did I give you the impression it does ?

should I have done something else?
As I indicated, please grant this user read access to master and user databases. Perhaps I forgot to mention that you should exclude master, model, tempdb and msdb from the database looping to get the list of tables of user databases. A db_datareader credential on master and all the user databases is then sufficient to get all user database names and to query all tables in user databases.

The emphasis is mine, but I read that as stating that I should grant read access to the user database which as far as I could tell does not exist?

I now see that by 'user databases' you meant each database that has been made available to any user. That does make sense... but it would appear to be an all or nothing approach, since if any database is not included in the list it would throw an error. I can perhaps trap that in my code, though, and exclude it on the basis that it is not available to the user. I will do some more experiments!

Alternatively, you can also target directly your user database by adding a DB_ID in the WHERE clause of the database level loop. In short, I like this kind of code because if you have a new database you need to query for your code generator, the code structure needs to be modified only in the WHERE clause to determine which database you include in the scope.

you can also target directly your user database by adding a DB_ID in the WHERE clause of the database level loop.

That would be good in a scenario where only one database is required or indeed just a few, but the situation is that there is the potential for an unlimited (apart from SQL Server limitations) number of databases to be added and I need to retrieve ALL matching ones so that could become one humungous WHERE clause!

Racino's answer did indeed provide a resolution exactly as I had requested, and it was a great answer. However, further experimentation after the fact whilst trying to reduce the number of steps required by a DBA setting up the system showed that simply moving some of the original application code inside the try... catch block provided a working solution without the need to add users to any of the system databases:

I still have to run some more detailed tests in live environments, but in the test environment this allows me to retrieve the list and to automatically exclude from the list databases to which the user has not been granted access rights.

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple. The final solution for this task involved creating a custom text parser and then reading…

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed