When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:
I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every database one at a time and retrieves information from sys.database_permissions
Is it possible to write a single query that retrieves the user mapping information shown above or am I forced to use a cursor or sp_MSforeachdb or something like that?
Best Answer
Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like
sp_MSforeachdb
(background here and here).This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.
On more modern versions (2017+), I would still use dynamic SQL, but I would use
STRING_AGG()
instead ofFOR XML PATH
, probably something like this:In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.