By Michael J Swart


2014-10-31 15:06:51 8 Comments

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 comments

@Nic 2014-10-31 15:23:06

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.

@dilipkumar katre 2018-12-17 07:49:18

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

@Michael J Swart 2018-12-17 14:39:13

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.

@dilipkumar katre 2018-12-19 07:32:32

Thank you for the feedback, I thought It can be repeated. Now Updated with improved query

@Michael J Swart 2018-12-19 16:52:18

See @Aaron-Bertrand's answer and comment on his thoughts about sp_MSforeachdb.

@Nacho 2016-07-05 13:09:45

What about EXEC master..sp_msloginmappings?

@Kin Shah 2016-07-05 15:16:07

Did you check sp_msloginmappings is undocumented and unsupported before posting ?

@Aaron Bertrand 2014-10-31 16:18:24

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.

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, p.default_schema_name, 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;

@Michael J Swart 2014-10-31 16:45:50

interesting note, I had to add explicit collations to p.name and p.default_schema_name columns for the union all to work properly

@Aaron Bertrand 2014-10-31 18:19:58

@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...

@NateJ 2016-02-24 20:16:15

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

@Ben Thul 2014-10-31 17:38:11

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
           }
       }
   }
}

@SqlWorldWide 2014-10-31 16:07:59

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' 

@Michael J Swart 2014-10-31 16:44:45

Thanks Taiob, this works well, (I would enclose the database_name column in brackets ('[' and ']')

@Kenneth Fisher 2014-10-31 15:34:20

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.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Assign Permissions to a SQL User

1 Answered Questions

SQL application connection using one Windows user

1 Answered Questions

2 Answered Questions

1 Answered Questions

[SOLVED] How keep overview about database permissions?

1 Answered Questions

1 Answered Questions

[SOLVED] Suppressing the SSMS prompt to save changes

1 Answered Questions

[SOLVED] Managing Access for DBA and IT Support

2 Answered Questions

[SOLVED] Can I restore the defult SQL Server Public Role?

Sponsored Content