Solved

Allowing application to browse and filter databases

Posted on 2013-06-25
10
300 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now