Showing posts from March, 2018

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 '