Show SQL Server Login Names and Type

-- --------------------------------------------------------------------------------------------------
-- 1. Show login names and their respective types on SQL Server
--
-- The following script lists all server-level logins, filters out built-in
-- server roles (like sysadmin), and returns the exact status of the login:
-- --------------------------------------------------------------------------------------------------

select name as [Login Name],
    type_desc as [Login Type],
    case
        when is_disabled = 1
             then 'Disabled'
        else 'Enabled'
    end as [Status],
    create_date as [Created Date],
    modify_date as [Last Modified]
from sys.server_principals
where type in ('S', 'U', 'G') -- S = SQL Login, U = Windows Login, G = Windows Group
order by name;

-- --------------------------------------------------------------------------------------------------
-- 2. Logins with Database-Level Roles Across All Databases
--
-- Because database users are stored separately inside each individual database,
-- you must query across every database on the instance to map logins to their
-- database roles (like db_owner or db_datareader).
-- --------------------------------------------------------------------------------------------------

create table #DbPermissions (
    [Database Name] sysname not null,
    [Login Name] sysname not null,
    [Login Type] nvarchar(60) not null,
    [Database User] sysname not null,
    [Database Role] sysname not null
);

exec sp_MSforeachdb @command1 = '
USE [?];
INSERT INTO #DbPermissions
SELECT
    DB_NAME(),
    sp.name,
    sp.type_desc,
    dp.name,
    ISNULL(r.name, ''public'')
FROM
    sys.database_principals dp
JOIN
    sys.server_principals sp ON dp.sid = sp.sid
LEFT JOIN
    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN
    sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE
    sp.type IN (''S'', ''U'', ''G'');
';

select [Database Name],
    [Login Name],
    [Login Type],
    [Database User],
    [Database Role]
from #DbPermissions
order by [Login Name],
    [Database Name];

drop table #DbPermissions;

-- --------------------------------------------------------------------------------------------------
-- 3. Logins with Database-Level Roles Across All Databases including sysadmin Logins & Groups
--
-- To include sysadmin groups and logins in your database audit, you can
-- explicitly pull sysadmin members for every database and union them with the
-- explicit database role assignments:
-- --------------------------------------------------------------------------------------------------

create table #DbPermissions (
    [Database Name] sysname not null,
    [Login Name] sysname not null,
    [Login Type] nvarchar(60) not null,
    [Database User] sysname not null,
    [Database Role] sysname not null
);

exec sp_MSforeachdb @command1 = '
USE [?];

-- 1. Explicit Database Users & Roles
INSERT INTO #DbPermissions
SELECT
    DB_NAME(),
    sp.name,
    sp.type_desc,
    dp.name,
    ISNULL(r.name, ''public'')
FROM
    sys.database_principals dp
JOIN
    sys.server_principals sp ON dp.sid = sp.sid
LEFT JOIN
    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN
    sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE
    sp.type IN (''S'', ''U'', ''G'')
    AND IS_SRVROLEMEMBER(''sysadmin'', sp.name) = 0

UNION ALL

-- 2. Implicit sysadmin Access (Mapped as dbo)
SELECT
    DB_NAME(),
    sp.name,
    sp.type_desc,
    ''dbo (via sysadmin)'',
    ''sysadmin (Full Control)''
FROM
    sys.server_principals sp
WHERE
    sp.type IN (''S'', ''U'', ''G'')
    AND IS_SRVROLEMEMBER(''sysadmin'', sp.name) = 1;
';

select * from #DbPermissions order by [Login Name], [Database Name];

drop table #DbPermissions;