When I use the GUI (SSMS) to review the User Mapping, where the Login has mapping to a database that is offline (or otherwise not available) I get the message below and it only displays Mapping for databases that are online.
One or more databases are inaccessible and will not be displayed in list
All the details should actually be in the Master DB, there should be something I can run that will show me what the full User Mapping is.
How can I display User Mapping for offline databases?
You can use sys.databases joined with sys.server_principals
SELECT sd.NAME AS databaseName
,sp.NAME AS database_owner
FROM sys.databases sd
INNER JOIN sys.server_principals sp ON sd.owner_sid = sp.SID
WHERE sd.state_desc = 'OFFLINE' --> since you want for offline databases !
You can even use at your own risk --> sp_msloginmappings ( <-- Undocumented and unsupported :-) )
You are making a false assumption here
All the details should actually be in the Master DB, there should be something I can run that will show me what the full User Mapping is.
Consider that if you restore a backup from one instance to another, the database brings over all the existing users (database detail) without there having been a login (instance detail) created.
Even if you could query the master database for user information about offline databases it would only include information where a login exists. Any users that have database user accounts without a login, would not be identified.
No one can read an offline database, your only option is to set the database to read-only, find what you want and set it back to offline (assuming it is offline for a reason)