I know this is a sort of odd request. I myself have never had to even think about a scenario like this until today. I'll go straight to the point to make the question easy to read. I suspect the right answer will involve more of "thinking outside the box" than a hidden feature or capability that somehow I'm not aware of.
Here is the thing: I need a PowerBI dashboard to query my SQL Server, but the PowerBI dashboard will have a connection string that queries a database (let's call this database "DatabaseOne_Report") whereas the SQL Server will host a database with a different name (let's say "DatabaseOne").
So when the PowerBI dashboard runs a query to DatabaseOne_Report.dbo.MyTable I want SQL Server to provide data from DatabaseOne.dbo.Table (notice the "_Report" suffix is gone from the database name). Is that even possible somehow?
I can anticipate your first question would be why I am using a connection string with the wrong database name. The answer is that this dashboard connects to different environments and these environments host this database using either 1 of 2 different names for reasons that are beyond the scope of what I'm asking here. The bottom line is that we are trying to avoid re-writing the connection string of the PowerBI dashboard and hope we can use a single connection string to tackle both possible DB names.
I read a lot of questions in different forums to tackle aliases of 2 databases hosted in different boxes, but never seen anybody asking 2 databases hosted in the same box but with potentially two different names.
I thought about creating a empty "shell" of a database with the other name with nothing but synonyms, but that would be a lot of configuration... I was hoping to find something simpler.