i am executing a query but it doesnt work like i want to

Erdem Erdemir 1

hi guys i am executing;
select name from sys.Databases
it only shows me system databases like master ect.
but i have lots of databases in my instance

Viorel 111.8K Reputation points

2022-05-14T05:14:55.807+00:00

Maybe the databases are offline or you do not have permissions?
Erdem Erdemir 1 Reputation point

2022-05-14T05:48:35.34+00:00

i am connecting with sa
Viorel 111.8K Reputation points

2022-05-14T09:18:54.293+00:00

Are you using "sa" in your C# tests too?
Seeya Xi-MSFT 16,436 Reputation points

2022-05-16T08:27:36.18+00:00

Hi @ErdemErdemir-5953 ,

Welcome to Microsoft Q&A!
Here is an article about C# Code: https://hevodata.com/learn/c-sql-server/

Best regards,
Seeya

2 answers

Jingyang Li 5,891 Reputation points

2022-05-14T03:05:56.463+00:00

You may have multiple instances on the same machine.
Double check that you are running this query in your instance correctly.
Please sign in to rate this answer.
Erdem Erdemir 1 Reputation point

2022-05-14T06:15:24.573+00:00

when i made this program i made users selecting the instance
When i execute this query on ssms there is no problem it shows every database but when i use c# to do it it only shows system databases
Sign in to comment
Erland Sommarskog 100.8K Reputation points MVP

2022-05-14T06:29:05.983+00:00
when i made this program i made users selecting the instance

When i execute this query on ssms there is no problem it shows every database but when i use c# to do it it only shows system databases

Debugging time, I guess!

First add this query to your C# program:

SELECT @@servername

to verify that you are connecting to the server you think you are connecting to.

Would that be the case, then add this query:

SELECT SYSTEM_USER, * FROM sys.fn_my_permissions(NULL, NULL)

Do you see VIEW ANY DATABASE in the list? Is the user the expected one?
Please sign in to rate this answer.
Erdem Erdemir 1 Reputation point

2022-05-14T07:14:11.753+00:00

yes it has the perm but when i executed in the c# it only says DESKTOP-DESKTOPNAME\CETRUSFORT
this is the code i run

SqlConnection con = new SqlConnection("Server= " + sss.sv + "; Database=; Integrated Security=True;");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT SYSTEM_USER, * FROM sys.fn_my_permissions(NULL, NULL)", con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
listBox1.Items.Add(dr[0].ToString());
}
con.Close();

Erland Sommarskog 100.8K Reputation points MVP

2022-05-14T08:32:00.88+00:00

yes it has the perm but when i executed in the c# it only says DESKTOP-DESKTOPNAME\CETRUSFORT

And what about @@servername? Is that the same as you see in SSMS?

Erdem Erdemir 1 Reputation point

2022-05-14T09:07:40.883+00:00

it says DESKTOP-DESKTOPNAME

Erland Sommarskog 100.8K Reputation points MVP

2022-05-14T11:03:38.39+00:00

Since I don't anything about your environment, that tells me nothing.

But to go back to the original problem. If SELECT name FROM sys.databases returns only the four system databases, there are two possible reasons:

The instance you connect to have no other databases. (Which could be because you are connecting to a different instance).

The user you are connecting with does not have have the permission VIEW ANY DATABASE. By permission is granted to public by default, but the DBA may have revoked that.

I gave you some tips on debugging to determine if any of these apply.
Sign in to comment