c# - Permissions necessary for conn.getSchema()? -

i'm trying list of databases using following code:

datatable databases = conn.getschema("databases"); 

for sql server.

that works has worked on other instances. it's not returning databases @ on 1 instance, though there dbs in it.

so, permissions/security rights necessary pull schema?

using sql server profiler, calling getschema("databases") causes following sql sent server:

exec sp_executesql   n'if object_id(''master..sysdatabases'') null       exec sp_executesql n''select name database_name, dbid, crdate create_date                             sysdatabases                             (name = @name or (@name null))'',                          n''@name nvarchar(128)'',                          @name=@name     else       exec sp_executesql n''select name database_name, dbid, crdate create_date                             master..sysdatabases                             (name = @name or (@name null))'',                          n''@name nvarchar(128)'',                          @name=@name',   n'@name nvarchar(4000)',   @name=null 

for sql statement run successfully, permissions needed execute sp_executesql stored procedure, execute select statements, , access sysdatabases view.

sp_executesql needs login account have membership in public role.

sysdatabases maps to sys.databases, has own set of permissions.

select has own set of permissions.

it's probable login account hasn't been granted ability view sys.databases, or execute select statements.

one way narrow down problem catch exceptions , examine innerexception property:

using system; using system.collections; using system.data.sqlclient;  namespace testprogram {   public class program   {     public static void main(string[] args)     {       using (var connection = new sqlconnection("server=(local);database=my_database;trusted_connection=true;"))       {         connection.open();          try         {           var databases = connection.getschema("databases");         }         catch (exception ex)         {           console.writeline(getallexceptionmessages(ex));         }       }     }      /* recursively build string of nested exception data, stack traces        , messages. */     private static string getallexceptionmessages(exception ex)     {       if (ex == null)       {         return "";       }       else       {         var nl = environment.newline;         var result = ex.message + nl;          foreach (dictionaryentry de in ex.data)           result += string.concat("  ", de.key, ": ", de.value, nl);          /* stacktrace might null when running code in nunit. */         if (ex.stacktrace != null)           result += string.format("{0}stack trace:{0}{1}", nl, ex.stacktrace.tostring());          return (string.concat(result, nl, getallexceptionmessages(ex.innerexception))).trim();       }     }   } } 

another option use sql server profiler , examine traffic between app , sql server. might pinpoint permissions necessary call getschema() successfully.

ultimately, have contact dba in charge of problematic sql server instance granted necessary permissions.