sql command yields empty results that aren't empty in sql server

Question

So, I'm trying to build a code generator that will extract indexes from a database and make a class that will filter based on an index. Below code works in SQL-server and yields 2 records. But my SqlDataReader yields zero records. Provided example for 1 table with index. Hoping someone can help me out here.

Code in SQL server:

create table Agent(
    ID bigint constraint PK_Agent primary key identity(1,1),
    LastName nvarchar(50) not null,
    FirstName nvarchar(50) not null,
    index IN_Agent_Name nonclustered (LastName, FirstName)
)

select t.object_id, 
        s.name as schemaname, 
        t.name as tablename,  
        i.index_id,  
        i.name as indexname,  
        index_column_id,  
        c.name as columnname  
from sys.tables t 
inner join sys.schemas s on t.schema_id = s.schema_id 
inner join sys.indexes i on i.object_id = t.object_id 
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id = i.index_id 
inner join sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id 
where i.index_id > 0 
and i.type in (1, 2)  
and i.is_primary_key = 0  
and i.is_unique_constraint = 0  
and i.is_disabled = 0  
and i.is_hypothetical = 0  
and ic.key_ordinal > 0  
and t.name like 'Agent'  
and i.name like 'IN_Agent_Name'

Code in VS:

        public static TableIndex GetIndex(string indexName, string tableName)
        {
            TableIndex index = null;
            using (var conn = new SqlConnection("Server=localhost;Database=VenturaERD;User Id=VenturaDBUser;Password = Ventura;"))
            {
                conn.Open();
                var cmd = new SqlCommand("select t.object_id, s.name as schemaname, t.name as tablename, i.index_id, i.name as indexname, index_column_id, c.name as columnname from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id = i.index_id inner join sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id where i.index_id > 0 and i.type in (1, 2) and i.is_primary_key = 0 and i.is_unique_constraint = 0 and i.is_disabled = 0 and i.is_hypothetical = 0 and ic.key_ordinal > 0 and t.name like '" + tableName + "' and i.name like '" + indexName + "'")
                {
                    Connection = conn
                };
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        index = new TableIndex()
                        {
                            TableId = reader.GetInt32(reader.GetOrdinal("object_id")),
                            TableName = reader.GetString(reader.GetOrdinal("tablename")),
                            IndexId = reader.GetInt32(reader.GetOrdinal("index_id")),
                            IndexName = reader.GetString(reader.GetOrdinal("indexname")),
                            Columns = new List()
                            {
                                new IndexColumn()
                                {
                                    ColumnName = reader.GetString(reader.GetOrdinal("columnname")),
                                    Order=reader.GetInt32(reader.GetOrdinal("index_column_id"))
                                }
                            }
                        };
                        while (reader.Read())
                        {
                            index.Columns.Add(new IndexColumn()
                            {
                                ColumnName = reader.GetString(reader.GetOrdinal("columnname")),
                                Order = reader.GetInt32(reader.GetOrdinal("index_column_id"))
                            });
                        }
                    }
                    reader.Close();
                }
            }
            return index;
        }

Answer 1

Please check the users rights, I believe with only the public rights the user will not get any data.

Make sure the user you are connecting to with SQL management studio and the user in the connection string are the same.

The user (in my tests at least) needs at least the db_datareader role.