Solved

Allowing application to browse and filter databases

Posted on 2013-06-25
10
309 Views
Last Modified: 2013-06-26
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.
0
Comment
Question by:chrisbray
  • 5
  • 4
10 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39275240
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275447
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
 
LVL 3

Author Comment

by:chrisbray
ID: 39277169
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 39277260
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
 
LVL 3

Author Comment

by:chrisbray
ID: 39277281
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
 
LVL 3

Author Closing Comment

by:chrisbray
ID: 39277287
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39277300
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
 
LVL 3

Author Comment

by:chrisbray
ID: 39277316
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
 
LVL 3

Author Comment

by:chrisbray
ID: 39277357
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39277366
Glad it helped :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question