SQL - reporte sql server

create procedure sp_listarusuarios_sistema

 as

DECLARE @DBNAME NVARCHAR(MAX)

declare @nur table ([servername] nvarchar(max), [nombre_database] nvarchar(max), [usuario] nvarchar(max),

[create_user_fecha] nvarchar(max), [rol] nvarchar(max));

DECLARE @SQL NVARCHAR(MAX)

DECLARE __DB_CUR__ CURSOR FOR

SELECT D.NAME FROM SYS.DATABASES D

OPEN __DB_CUR__

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @SQL =

 (';with dbr as (select * from [' + @DBNAME + '].sys.database_principals where type = ''R'')

 ,dbu as (select * from [' + @DBNAME + '].sys.database_principals where type  IN (''S'', ''U'', ''G''))

 SELECT @@servername servername, ''' + @DBNAME + ''' db, u.name [user], u.create_date [fecha], r.name [role] FROM DBU u

  left join [' + @DBNAME + '].sys.database_role_members rl

   on u.principal_id = rl.member_principal_id

  left join dbr r on

   rl.role_principal_id = r.principal_id

 order by [user], [role]

 ;')

 INSERT INTO @nur

 EXEC (@SQL)

 FETCH NEXT FROM __DB_CUR__ INTO @DBNAME

END

CLOSE __DB_CUR__

DEALLOCATE __DB_CUR__

SELECT * FROM @nur

go

exec sp_listarusuarios_sistema

DECLARE @exec                            NVARCHAR(MAX) = ''

DECLARE @Roles                                  NVARCHAR(MAX) = ''

DECLARE       @nurSQL                                  NVARCHAR(max) = ''

DECLARE @SQL_Bucle                       NVARCHAR(MAX) = ''

DECLARE @SQL_Roles                       NVARCHAR(MAX) = ''

declare @nur table ([Servidor] SYSNAME

,     [nombre_database]                 SYSNAME

,     [usuario]                                SYSNAME

,     Fecha_creación_usuario            DATE

,     Fecha_modificación_usuario DATE

,     [tipo_usuario]                           CHAR (1)

,     [rol]                                    NVARCHAR(255));

----------------------------------------------------------------------------------------------------

-- Esta sentencia de declara la consulta para sacar los permisos de una BD. La usaré como plantilla

-- para cada BD en to instancia del servidor.

-- Verás que ago uso de la variable @SQL_bucle con los resultados de una consulta al servidor para

-- generar una lista de todas las BD en el servidor.

----------------------------------------------------------------------------------------------------

SET @SQL_Bucle =

('

SELECT              @@SERVERNAME                                                  [Servidor]

,                   ''{DBNAME}''  COLLATE DATABASE_DEFAULT   [Nombre_database]

,                   u.name               COLLATE DATABASE_DEFAULT   [Usuario]            

,                   u.create_date                                                 [Fecha_creación_usuario]   

,                   u.modify_date                                                 [Fecha_modificación_usuario]      

,                   u.type               COLLATE DATABASE_DEFAULT   [Tipo_usuario]       

,                   r.name               COLLATE DATABASE_DEFAULT   [Rol]         

FROM         [{DBNAME}].SYS.database_principals U     

LEFT JOIN  [{DBNAME}].SYS.database_role_members RL ON U.principal_id = RL.member_principal_id

LEFT JOIN    [{DBNAME}].SYS.DATABASE_PRINCIPALS R ON RL.role_principal_id = R.principal_id

WHERE r.name IS NOT NULL

'

)

----------------------------------------------------------------------------------------------------

-- ahora genero una segunda variable que utilizaré para hacer el PIVOT

-- haremos un replace() de los campos indicados entre parentesis {}

-- por no pasa demasiado tiempo con una solución no me importa si repito la consulta @sql_bucle dos

-- veces; una vez para generar la lista de roles, y una segunda vez para el pivot. Así me ahorro

-- complicaciones con el sql dinámico.

SET @SQL_Roles = N'

select Servidor

, nombre_database

, usuario

, tipo_usuario

,Fecha_creación_usuario

,Fecha_modificación_usuario

,{rols}

from

( select Servidor, nombre_database, usuario, tipo_usuario, Fecha_creación_usuario, Fecha_modificación_usuario, rol, ''x'' indicador FROM ({nur}) p

where tipo_usuario <> ''R'') as source

pivot

(max(indicador)

for rol in ({rols})

) as pivotTable;

'

----------------------------------------------------------------------------------------------------

-- Aquí hago ejecuto la sentencia @nur para crear el listado de consultas dirigido a todas las BDs del servidor

SELECT @nurSQL += CASE WHEN row_number() over (order by database_id desc) > 1 then replace(@SQL_Bucle,'{dbname}',name) + char(13) + ' UNION ALL'

                                  ELSE REPLACE(@SQL_Bucle,'{dbname}',name)   END

FROM SYS.DATABASES

WHERE COMPATIBILITY_LEVEL = 110 -- si no ves resultados, pude ser este numero. Cambialo en funcion de tu version de SQL server.

ORDER BY database_id ASC

INSERT INTO @nur -- aquí almaceno el resultado de la consulta para extraer los roles.

  EXEC(@nurSQL)

----------------------------------------------------------------------------------------------------

-- aquí convierto el resultado de roles en una variable varchar para luego usar la función replace con la plantilla @SQL_Roles

SELECT @Roles +=  case when row_number() over (order by rol_dist desc) > 1 then  rol_dist + ',' + char(13) else rol_dist + char(13) end

FROM   (

              SELECT DISTINCT rol_dist = QUOTENAME(rol)

              FROM @nur

              WHERE rol is not null

              ) rols

ORDER BY rol_dist ASC

----------------------------------------------------------------------------------------------------

-- Genero la sentencia pivot con las variables necesarias en éste último set y ya termino ejecutando

-- el marrón éste :D

SET @SQL_Bucle = replace(replace(@SQL_Bucle,'{rols}',@roles),'{nur}',@nurSQL)

EXEC (@SQL_Bucle)