How do I list databases on SQL Azure server using SMO when not all databases have the user used to connect to the server?

Question

SMO's Server.Databases will throw an exception if one database on the server does not have the SQL User I've used to create the SMO server connection with.

I don't get this same error to occur if using a local SQL 2017 instance.

For example:

I have a SQL Azure server named 'myazuresqlserver.database.windows.net'

1. Create a login on master:

   CREATE LOGIN testlogin WITH PASSWORD = 'ThisIsMySecretPassword'

2. Create 2 databases, DB1 and DB2.

3. Create the User 'testuser' in DB1 but NOT DB2:

   CREATE USER testuser FOR LOGIN testlogin

Then, using C# and SMO I cannot get a list of databases for that server using the credentials for the testlogin login - an exception occurs.

static void Main(string[] args)
{
    string[] names = GetDatabaseNames("myazuresqlserver.database.windows.net");
}

public static string[] GetDatabaseNames(string serverName)
{       
    ServerConnection connection = new ServerConnection(serverName, "testuser", "ThisIsMySecretPassword");
    var server = new Server(connection);
    return (from Database database in server.Databases
        where !database.IsSystemObject && !database.IsDatabaseSnapshot
        select database.Name
       ).ToArray();
}

The exception is:

SqlException: The server principal "testlogin" is not able to access the database "DB2" under the current security context.
Cannot open database "DB2" requested by the login. The login failed. Login failed for user 'testlogin'.

I would have expected no exception to be thrown, and instead the Server.Databases to only contain databases which the credentials provided were valid - this seems to be the behaviour for a local SQL 2017 instance.

Answer 1

I came across the same issue. It's as if attempting to look at Server.Databases at all causes enumeration to occur, and in doing so database properties are being looked at - properties that the user may not have permission to interrogate. An exception is thrown immediately.

I ended up using a different technique to get a list of databases. I simply fired a sql query:

SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

For the above query to work, the user must have permission to access the master database. Here is my code block:

Dim Server As Server = New Server(New ServerConnection("myserver.server.net", "testlogin", "password"))
Dim databaseList As New DataTable
databaseList.Columns.Add("Name")

If Server.Edition = "SQL Azure" Then
    Dim SQLReader As SqlClient.SqlDataReader = Nothing
    Try
        SQLReader = Server.ConnectionContext.ExecuteReader("SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');")

        Do While SQLReader.Read = True
            databaseList.Rows.Add(Manager.Database.Sanitise(SQLReader, "name").ToString)
        Loop

        SQLReader.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        If Not SQLReader Is Nothing Then
            SQLReader.Close()
        End If
    End Try
End If