Link to home
Start Free TrialLog in
Avatar of chrisbray
chrisbrayFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

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.
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.
Avatar of chrisbray

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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!
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.
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!
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.
Glad it helped :)