Query to Expand and List All logins within a SQL Server Group

This query will get the details of user logins with in Active directory security group in expanded format which is not available by default in SSMS, this will iterate over all the user logins under Security section and expand and list the users within the group if there are any along with all other logins:
For example DBA team has 10 members who are part of Group Name DBA_GROUP and this Group is added in SQL Server logins so it will list the members with in DBA_GROUP as well (like DOMAIN\DBA1 DOMAIN\DBA2 and so on )instead of just listing DBA_GROUP.

SET NOCOUNT ON
CREATE TABLE ##TEMPHOLDUSERS
      (
       AccountName NVARCHAR(MAX),
       AccoutnType NVARCHAR(MAX),
       Privilege NVARCHAR(MAX),
       MappedLoginName NVARCHAR(MAX),
       PermissionPath NVARCHAR(MAX)
      )

DECLARE @id NVARCHAR(MAX)
DECLARE @getcoinid  CURSOR

SET @getcoinid   = CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE TYPE = 'G' AND name NOT LIKE '%$%' and name NOT LIKE 'NT%'

OPEN @getcoinid 
FETCH NEXT
FROM @getcoinid  INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO ##TEMPHOLDUSERS EXEC xp_logininfo @id, 'members'
    FETCH NEXT
    FROM @getcoinid INTO @id
END
INSERT INTO ##TEMPHOLDUSERS EXEC xp_logininfo

SELECT AccountName ,
       AccoutnType ,
       Privilege ,
       MappedLoginName,
       CASE WHEN PermissionPath
       IS NULL THEN 'This is a list of all logins type including group logins expanded above and other service type that has nothing to expand'
       ELSE PermissionPath
END PermissionPath
FROM ##TEMPHOLDUSERS
CLOSE @getcoinid
DEALLOCATE @getcoinid
DROP TABLE ##TEMPHOLDUSERS

Comments

Popular posts from this blog

Adding Home Older Newer Post Buttons in new Blogger templates such as Contempo

Adding copy to clipboard button to every google code prettify pre blocks in blogger

Checklist: Before applying for adsense