A query that lists all mapped users for a given login

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?

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;
    
    ;WITH d AS 
    (
      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 ')
    FROM d;
    
    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.


  • 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/

        SET NOCOUNT ON
        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)
            )
    
        DECLARE @command VARCHAR(MAX)
        --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(),
                         [email protected]@ServerName
        where database_name is null
        '
        FROM    @databases
        EXECUTE ( @command )
    
        SELECT  loginname ,
                UserName ,
                Database_name
        FROM    #temp
        WHERE   LoginName = 'ThursdayClass' 
    

  • 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)
    
    INSERT @DB_Users
    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 = 
    dbPri.principal_id
    JOIN [DbName].sys.database_principals dbPri1  ON dbPri1.principal_id = 
    dbRoleMem.role_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 = 
     dbPri.principal_id
     JOIN sys.database_principals dbPri1  ON dbPri1.principal_id = 
     dbRoleMem.role_principal_id
     WHERE dbPri.name != ''dbo'''
    
     EXEC sp_MSforeachdb @sql
    


Related Questions