Getting List of All Logins Under All Login and All Windows Group in SQL Server

Hello to everyone,

In this article, I will try to give information about getting a list of all logins and all logins under all windows group in SQL Server.

In SQL Server, in some cases you may want to get a list of all logins under all logins and all windows group.

You can easily do this using the code below.

DECLARE @LoginList TABLE
(
    LoginName NVARCHAR(256),
    [Type] VARCHAR(8),
    [Privilege] VARCHAR(8),
    [Mapped Login Name] NVARCHAR(256),
    WindowsGroupName NVARCHAR(256)
);

DECLARE @WindowsGroupName NVARCHAR(256);

--SQL/Windows Logins

INSERT INTO @LoginList
SELECT name,
       'user',
       'user',
       name,
       ''
FROM sys.server_principals
WHERE type IN ( 'S', 'U' );
DECLARE c1 CURSOR FOR

--Windows Groups

SELECT name
FROM sys.server_principals
WHERE type = 'G';
OPEN c1;
FETCH NEXT FROM c1
INTO @WindowsGroupName;
WHILE @@fetch_status <> -1
BEGIN
    INSERT INTO @LoginList
    (
        LoginName,
        [Type],
        [Privilege],
        [Mapped Login Name],
        WindowsGroupName
    )
    EXEC xp_logininfo @acctname = @WindowsGroupName, @option = 'members';
    FETCH NEXT FROM c1
    INTO @WindowsGroupName;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT LoginName,
       WindowsGroupName
FROM @LoginList;

When you run the above code block, you will see the following result.

As you can see, the list of all logins and all logins under all windows group has been taken.

Good luck to everyone in business and life.