Allowing application to browse and filter databases

Hi Guys,

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?

I get the list of databases using GetSchema:

DataTable retrievedDatabases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);

Open in new window


I then review the list and retrieve either the whole list or the filtered list:

var databaseList = new List<string>();
const string compulsoryTable = "TableIExpectToFindInMatchingDatabases";
restrictions[0] = databaseName;
restrictions[1] = "dbo";
restrictions[2] = compulsoryTable;
restrictions[3] = "BASE TABLE";

foreach (DataRow databaseRow in retrievedDatabases.Rows)
{
    string databaseName = databaseRow[0].ToString();
    connection.ChangeDatabase(databaseName);
    restrictions[0] = databaseName;

    try
    {
        if (connection.GetSchema(SqlClientMetaDataCollectionNames.Tables, restrictions).Rows.Count > 0)
         {
             databaseList.Add(databaseName);
          }
      }
      catch (SqlException exception)
      {
          Debug.WriteLine(exception.Message + " - " + exception.StackTrace);
       }
}

Open in new window


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!

All advice and suggestions would be appreciated.
LVL 3
chrisbrayAsked:
Who is Participating?
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
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.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Ask your DBA's to create an login and users for your application and to grant this user read access to master and user databases.
0
 
ZberteocCommented:
You can run this to get the database names:

select name from master..sysdatabases where dbid>4

Open in new window

You don`t need any special permission for that, public role with connect rights, which is minimum and default, will suffice.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
chrisbrayAuthor Commented:
Hi Guys,

Racimo:

System databases do not include a user database.  Options are:

master
model
msdb
tempdb

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?

Chris Bray
0
 
chrisbrayAuthor Commented:
Hi Racimo,

> Where did I give you the impression it does ?

When you said in both replies:

grant this user read access to master and 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!

Chris Bray
0
 
chrisbrayAuthor Commented:
Thank you, that does indeed solve the issue and adding code to ignore databases to which the user has no access is pretty much trivial so that is exactly the solution I requested.

Thank you also for the prompt and patient responses!
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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.

Hope this helps.
0
 
chrisbrayAuthor Commented:
Hi Racino,

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!
0
 
chrisbrayAuthor Commented:
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:

var databaseList = new List<string>();
const string compulsoryTable = "TableIExpectToFindInMatchingDatabases";
restrictions[0] = databaseName;
restrictions[1] = "dbo";
restrictions[2] = compulsoryTable;
restrictions[3] = "BASE TABLE";

foreach (DataRow databaseRow in retrievedDatabases.Rows)
{
    try
    {
        string databaseName = databaseRow[0].ToString();
        connection.ChangeDatabase(databaseName);
        restrictions[0] = databaseName;

        if (connection.GetSchema(SqlClientMetaDataCollectionNames.Tables, restrictions).Rows.Count > 0)
         {
             databaseList.Add(databaseName);
          }
      }
      catch (SqlException exception)
      {
          Debug.WriteLine(exception.Message + " - " + exception.StackTrace);
       }
}

Open in new window


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.

I hope this helps someone facing a similar issue.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Glad it helped :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.