Filter SQL server databases based on user permissions

Data and Databases
1

I’m using Power Query to connect to a SQL server and retrieve all available databases. My current code successfully lists the databases, but I run into issues when trying to expand the data column because I lack permissions for some databases.

The problem occurs when I click the expand button; the operation fails due to access restrictions on certain databases. I’ve tried various solutions including adding columns with functions like Table.IsEmpty, but none of these methods worked.

Is there a way to automatically exclude databases that I don’t have read access to? This filtering needs to be dynamic so it adjusts whenever my permissions change.

Here’s my current code:

let
    DatabaseList = Sql.Databases("myserver", [HierarchicalNavigation=true])
in
    DatabaseList
2

You need error handling in your Power Query pipeline. Add a custom column that checks if each database is accessible before you try to expand anything. Use Table.AddColumn(DatabaseList, "IsAccessible", each try not Table.IsEmpty([Data]) otherwise false) then filter by that boolean column. This tests each database separately and marks the broken ones as false instead of crashing your whole query. Once you’ve filtered out the bad ones, you can expand the Data column on what’s left. It’ll automatically adjust when your permissions change - no manual fixes needed.

3

Had the same problem. Add a custom column with try Table.RowCount([Data]) > 0 otherwise false then filter where that column = true. Works great for filtering out inaccessible databases without errors.

4

interesting challenge! try wrapping your database access in try-catch: try Sql.Database(server, dbname) otherwise null should filter out the restricted ones. what power query version are you using?