Wednesday 15 August 2007

Auditing Groups Membership from SQL Server

MSSQLTips publish this cursor based solution for seeing group membership from within SQL.

I have enhanced it to -
1) Not Error (the sql 2005 try/catch statements)
2) Utilise a table variable to store the results
3) Return a queryable recordset

Note :
This sql reveals accounts that are members of Windows group logins on this server.
It does not reveal group members in child domains or on other domains.

DECLARE  @SqlGroupMembership  TABLE(
   ACCOUNT_NAME      SYSNAME,
   ACCOUNT_TYPE      VARCHAR(30),
   ACCOUNT_PRIVILEGE VARCHAR(30),
   MAPPED_LOGIN_NAME SYSNAME,
   PERMISSION_PATH   SYSNAME
   )

DECLARE @LoginName sysname

DECLARE cur_Loginfetch CURSOR FOR   
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G'
OPEN cur_Loginfetch
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
  BEGIN
    BEGIN TRY
    -- Insert found users into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
      EXEC xp_logininfo @LoginName , 'members'
    END TRY

    BEGIN CATCH
    -- Action for if insert fails
      END CATCH

      FETCH NEXT FROM cur_Loginfetch INTO @LoginName
  END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
 
SELECT @@servername as servername,* FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]

No comments: