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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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 🙂
