When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: enter image description here

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?


8 Answers 8


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.

DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS, 
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
    STUFF((SELECT N'','' + r.name 
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:

DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max), 
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ', 
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

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.

  • 2
    interesting note, I had to add explicit collations to p.name and p.default_schema_name columns for the union all to work properly Oct 31, 2014 at 16:45
  • 1
    @MichaelJSwart Ah yes, I've come across this before when databases have different collations (some metadata columns use the server collation but others inherit the database collation). I hope the only people that really ever burns are those that insist on using wacky characters in entity names that are only supported in some obscure collation... Oct 31, 2014 at 18:19

This script is slightly modified from a script mentioned at will do what you are looking for. Replace 'ThursdayClass' with the login you need info for. https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/

    CREATE TABLE #temp
          SERVER_name SYSNAME NULL ,
          Database_name SYSNAME NULL ,
          UserName SYSNAME ,
          GroupName SYSNAME ,
          LoginName SYSNAME NULL ,
          DefDBName SYSNAME NULL ,
          DefSchemaName SYSNAME NULL ,
          UserID INT ,
          [SID] VARBINARY(85)

    --this will contain all the databases (and their sizes!)
    --on a server
    DECLARE @databases TABLE
          Database_name VARCHAR(128) ,
          Database_size INT ,
          remarks VARCHAR(255)
    INSERT  INTO @databases--stock the table with the list of databases
            EXEC sp_databases

    SELECT  @command = COALESCE(@command, '') + '
    USE ' + database_name + '
    insert into #temp (UserName,GroupName, LoginName,
                        DefDBName, DefSchemaName,UserID,[SID])
         Execute sp_helpuser
    UPDATE #TEMP SET database_name=DB_NAME(),
    where database_name is null
    FROM    @databases
    EXECUTE ( @command )

    SELECT  loginname ,
            UserName ,
    FROM    #temp
    WHERE   LoginName = 'ThursdayClass' 
  • Thanks Taiob, this works well, (I would enclose the database_name column in brackets ('[' and ']') Oct 31, 2014 at 16:44

Try sp_dbpermissions. It will probably give you more info than you need but it will do what you want.

Once it's installed run this.

sp_dbpermissions @dbname = 'All', @LoginName = 'LoginName'

Fair warning at the moment it does a "like" match so if other logins are similar and match then you will see them also. For example MyLogin and MyLoginForThis will both match on MyLogin. If that's a problem I have a version that I haven't released yet where you can turn that off. Let me know and I can email it to you.


Here's a powershell solution:

import-module sqlps;

$s = new-object microsoft.sqlserver.management.smo.server '.'
foreach ($db in $s.Databases | where {$_.IsAccessible -eq $true}) {
   $u = $db.users | where {$_.Login -eq 'foobar'}
   if ($u -ne $null) { #login is mapped to a user in the db
       foreach ($role in $db.Roles) {
           if ($role.EnumMembers() -contains $u.Name) {
               $u | select parent, @{name="role";expression={$role.name}}, name

Sadly you are going to have to iterate through all of the databases in order to get the information. You'll want to join sys.database_principals to sys.server_principals for each database matching on the SID.

Don't use sp_msforeachdb as it is known to miss databases at times.


I was searching for a similar answer and found this: https://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/ . And yes, it uses the dreaded sp_MSforeachDB, but I think that guy gets a bad rap sometimes... ;-)

I'll post the SQL here for easy copy-pasta (I am NOT taking credit for this, just making it easily accessible!):

DECLARE @DB_Users TABLE (DBName sysname, UserName sysname, LoginType sysname
, AssociatedRole varchar(max), create_date datetime, modify_date datetime)

EXEC sp_MSforeachdb
'use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''
    + (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')''
    else prin.name end AS UserName,
    prin.type_desc AS LoginType,
    isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole, 
    create_date, modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem
    ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00)
and prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT dbname, username, logintype, create_date, modify_date,
    STUFF((SELECT ',' + CONVERT(VARCHAR(500), associatedrole)
        FROM @DB_Users user2
        WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
        FOR XML PATH('')
    ),1,1,'') AS Permissions_user
FROM @DB_Users user1
WHERE user1.UserName = N'<put your login-name here!>'
GROUP BY dbname, username, logintype, create_date, modify_date
ORDER BY DBName, username

Below Query shall return the mappings for the requested DbName

SELECT 'DbName', dbPri.name, dbPri1.name
FROM [DbName].sys.database_principals dbPri 
JOIN [DbName].sys.database_role_members dbRoleMem ON dbRoleMem.member_principal_id = 
JOIN [DbName].sys.database_principals dbPri1  ON dbPri1.principal_id = 
WHERE dbPri.name != 'dbo'

Improved Query is below

declare @sql varchar(Max)

 set @sql = 'use ? SELECT ''?'', dbPri.name, dbPri1.name
 FROM sys.database_principals dbPri 
 JOIN sys.database_role_members dbRoleMem ON 
 dbRoleMem.member_principal_id = 
 JOIN sys.database_principals dbPri1  ON dbPri1.principal_id = 
 WHERE dbPri.name != ''dbo'''

 EXEC sp_MSforeachdb @sql
  • Thank you, the goal of this script was to find a mapping for all databases. Your script gives information for just one specific database, not all of them. Dec 17, 2018 at 14:39
  • Thank you for the feedback, I thought It can be repeated. Now Updated with improved query Dec 19, 2018 at 7:32
  • See @Aaron-Bertrand's answer and comment on his thoughts about sp_MSforeachdb. Dec 19, 2018 at 16:52

What about EXEC master..sp_msloginmappings?

  • Did you check sp_msloginmappings is undocumented and unsupported before posting ?
    – Kin Shah
    Jul 5, 2016 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.