All Logins With DB Users

Declare @Login sysname,
    @LoginType char(1),
    @CurrID int,
    @MaxID int,
    @DBName sysname,
    @SQL nvarchar(1000);

Declare @Logins Table (LoginID int not null identity(1, 1) primary key,
                        AcctName sysname not null,
                        LoginType char(1))

Declare @LogInfo Table (AcctID int not null identity(1, 1) primary key,
                        AcctName sysname not null, -- Fully qualified Windows account name
                        AcctType varchar(50) not null, -- Type of Windows account. Valid values are user or group
                        Privilege char(9) not null, -- Access privilege for SQL Server. Valid values are admin, user, or null
                        MappedLogin sysname not null, -- For user accounts that have user privilege, mapped login name shows the mapped login name that SQL Server tries to use when logging in with this account by using the mapped rules with the domain name added before it
                        PermissionPath sysname null, -- Group membership that allowed the account access
                        SysID varbinary(85) null,
                        PrincipalID int null,
                        DefaultDB sysname null,
                        IsDisabled bit not null default(0));

Declare @ServerRoles Table (SRoleID int identity(1, 1) not null primary key,
                            AcctID int not null,
                            RoleName sysname not null);

Declare @DBs Table (DBsID int identity(1, 1) not null primary key,
                    DBName sysname not null);

Declare @DBRoles Table (DBRoleID int identity(1, 1) not null primary key,
                    SysID varbinary(85) null,
                    DBName sysname not null,
                    RoleName sysname not null default('Public'),
                    DBUser sysname null);

Set NoCount On;

Insert Into @Logins (AcctName, LoginType)
Select name, type
From sys.server_principals
Where type In ('S', 'U', 'G', 'C')
And name not like N'##%';

Select @MaxID = Max(LoginID),
    @CurrID = 1
From @Logins;

While @CurrID <= @MaxID
  Begin
    Select @Login = AcctName,
        @LoginType = LoginType
    From @Logins
    Where LoginID = @CurrID;

    If @LoginType In ('U', 'G')
      Begin
        Begin Try
            Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
            Exec xp_logininfo @Login, 'all';
        End Try
        Begin Catch
            Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
            Select @Login,
                Case @LoginType When 'U' Then 'User'
                    When 'G' Then 'Group'
                End,
                Case IS_SRVROLEMEMBER(N'sysadmin', @Login) When 0 Then 'user'
                    When 1 Then 'admin'
                End,
                @Login,
                Null;
        End Catch
      End
    Else
      Begin
        Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
        Select @Login,
            Case @LoginType When 'S' Then 'SQL User'
                When 'C' Then 'Certificate Mapped User'
            End,
            Case IS_SRVROLEMEMBER(N'sysadmin', @Login) When 0 Then 'user'
                    When 1 Then 'admin'
                End,
            @Login,
            Null;
      End

    Set @CurrID = @CurrID + 1;
  End

If Exists (Select 1 From @LogInfo)
  Begin
    Update @LogInfo
    Set PermissionPath = MappedLogin
    Where PermissionPath Is Null;

    Update LI
    Set SysID = SID, PrincipalID = principal_id, DefaultDB = default_database_name, IsDisabled = is_disabled
    From @LogInfo LI
    Inner Join sys.server_principals SP On SP.name = LI.PermissionPath;

    Insert Into @ServerRoles (AcctID, RoleName)
    Select LI.AcctID, SP.name
    From @LogInfo LI
    Inner Join sys.server_role_members RM On RM.member_principal_id = LI.PrincipalID
    Inner Join sys.server_principals SP On SP.principal_id = RM.role_principal_id
    Where SP.type = 'R';

    Insert Into @DBs (DBName)
    Select name
    From sys.databases;

    Select @MaxID = Max(DBsID),
        @CurrID = 1
    From @DBs;

    While @CurrID <= @MaxID
      Begin
        Select @DBName = DBName
        From @DBs
        Where DBsID = @CurrID;

        Set @SQL = 'Select ''' + @DBName + ''', IsNull(DRP.name, ''Public''), DP.sid, DP.name
                    From ' + quotename(@DBName) + '.sys.database_principals DP
                    Left Join ' + quotename(@DBName) + '.sys.database_role_members RM On RM.member_principal_id = DP.principal_id
                    Left Join ' + quotename(@DBName) + '.sys.database_principals DRP On DRP.principal_id = RM.role_principal_id
                    Where (DRP.type = ''R'' Or DRP.type Is Null)
                    And DP.Type In (''U'', ''G'', ''S'', ''C'');';

        Insert Into @DBRoles (DBName, RoleName, SysID, DBUser)
        Exec sp_executesql @SQL;

        Set @CurrID = @CurrID + 1;
      End

    Select AcctName As [Account Name], AcctType As [Account Type], Privilege As [Highest Privilege], PermissionPath As [Parent Login], DefaultDB As [Default DB]
    From @LogInfo;

    If Exists (Select 1 From @ServerRoles SR
                Inner Join @LogInfo LI On LI.AcctID = SR.AcctID)
        Or Exists (Select 1 From @LogInfo LI
                    Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
                    Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
                    Where SP.type In ('G', 'U', 'S', 'C')
                    And PM.state = 'G'
                    And PM.type <> 'COSQ')
      Begin
        Select LI.AcctName As [Account Name], LI.PermissionPath As [Parent Login], RoleName As [Server Role]
        From @ServerRoles SR
        Inner Join @LogInfo LI On LI.AcctID = SR.AcctID
        Union
        Select LI.AcctName As [Account Name], SP.[name], Cast(PM.permission_name as sysname) Collate SQL_Latin1_General_CP1_CI_AS
        From @LogInfo LI
        Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
        Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
        Where SP.type In ('G', 'U', 'S', 'C')
        And PM.state = 'G'
        And PM.type <> 'COSQ';
      End

    If Exists (Select 1 From @DBRoles DR
                Inner Join @LogInfo LI On LI.SysID = DR.SysID)
      Begin
        Select LI.AcctName As [Account Name], DR.DBName As [Database], LI.PermissionPath As [Parent Login], RoleName As [Database Role], DBUser As [Database user]
        From @DBRoles DR
        Inner Join @LogInfo LI On LI.SysID = DR.SysID;
      End
 End

Set NoCount Off;