Obtener grupos de dominio y sus usuarios con rol sysadmin

A menudo como DBAs nos toca realizar pequeños trabajo de auditoria sobre nuestras bases de datos o instancias. Hace unas semanas, uno de nuestros clientes nos solicitó obtener todos los usuarios con rol sysadmin sobre una instancia concreta. Dichos datos se pueden obtener a través de la vista de sistema sys.syslogins.

Normalmente trabajamos con logins tanto del propio SQL Server como de dominio. Entonces ¿qué ocurre si trabajamos con grupos de nuestro directorio activo? ¿Cómo obtenemos la información de los usuarios de dichos grupos?

Esta información la podemos obtener a través de SQL Server con el procedimiento almacenado incluído en la base de datos master xp_logininfo:

El cual nos da, tras obtener la información de nuestros inicios de sesión, la información de los grupos de seguridad de Windows. Para ello necesitamos tener permisos sobre el directorio activo (AD) de nuestro dominio. Si la cuenta de servicio del SQL Server no tiene permisos, nos mostrará un error similar a este:

Una vez comprobados, u otorgados, los permisos en AD podemos lanzar la siguiente query:

DECLARE @LoginName sysname 
DECLARE @sql NVARCHAR (3000) 

BEGIN 
   DECLARE cur_Loginfetch CURSOR FOR 
    
   SELECT [name] FROM master.dbo.syslogins WHERE isntgroup = 1 and sysadmin = 1
    
   OPEN cur_Loginfetch 
    
   FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
   WHILE @@FETCH_STATUS = 0 
       BEGIN 
           EXEC xp_logininfo @LoginName , 'members' 
           FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
       END 
   CLOSE cur_Loginfetch 
   DEALLOCATE cur_Loginfetch 
   RETURN 
END

Como podemos observar en el resultado de la ejecución de la query, obtenemos los login y usuarios de los grupos de seguridad de dominio que tienen privilegios de sysadmin sobre nuestra instancia:

También, como ya tratamos en la entrada de ‘SP_WHO y cómo filtrarlo’ , podemos realizar una tabla temporal y manipular nuestros datos. Os dejamos cómo sería dicho script:

CREATE TABLE #sysadmin 
  ( 
     [account name]      VARCHAR(255), 
     [type]              VARCHAR(255), 
     [privilege]         VARCHAR(255), 
     [mapped login name] VARCHAR(255), 
     [permission path]   VARCHAR(255) 
  ) 

DECLARE @LoginName SYSNAME 
DECLARE @sql NVARCHAR (3000) 
DECLARE cur_loginfetch CURSOR FOR 
  SELECT [name] FROM   master.dbo.syslogins WHERE  isntgroup = 1 AND sysadmin = 1 

OPEN cur_loginfetch 

FETCH next FROM cur_loginfetch INTO @LoginName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      INSERT INTO #sysadmin 
      EXEC Xp_logininfo @LoginName,'members' 

      FETCH next FROM cur_loginfetch INTO @LoginName 
  END 

-- A continuación podemos añadir filtros (WHERE por ej.) o instrucciones (ORDER por ej.) 
SELECT @@SERVERNAME      AS Instancia, 
       [account name], 
       [permission path] AS GrupoAD 
FROM   #sysadmin; 
-- Importante: Borrar la tabla temporal
DROP TABLE #sysadmin; 

CLOSE cur_loginfetch 

DEALLOCATE cur_loginfetch 

RETURN

Esperemos que os haya servido de utilidad tanto como a nosotros.

Si tenéis cualquier sugerencia o duda lanzarnos un correo o un comentario en este post 🙂