SQL - reporte sql server

 
Vista:
sin imagen de perfil
Val: 15
Ha aumentado su posición en 45 puestos en SQL (en relación al último mes)
Gráfica de SQL

reporte sql server

Publicado por Juan (6 intervenciones) el 14/03/2019 15:57:48
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
40
41
42
43
44
45
46
47
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

tengo este store procedure que me bota la lista de los usuarios con sus respectivas base de datos pero quiero utilizar una sentencia pivot para los roles si alguien me podria ayudar.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Vega
Val: 54
Ha aumentado su posición en 8 puestos en SQL (en relación al último mes)
Gráfica de SQL

reporte sql server

Publicado por Vega (22 intervenciones) el 15/03/2019 14:38:40
Hola Juan,

¿Podrías decirnos algo más sobre lo que quieres ver los resultados por favor? ¿Qué campos quieres ver en los ejes, y si es el usuario en sí o la suma de los usuarios asignados al rol por base de datos?
¿Tienes pensado visualizar la información directamente en SSMS o vas a usar un interfaz SSRS or parecido para presentar la información? En cuyo caso hacer el pivot fuera de SQL es mas efficiente

También, si me lo permites, te comento unos ajustes y sugerencias para el contenido del proceso almacenado que tienes:
Primero, te aconsejaría que evitaras el uso de cursores. Intenta general el SQL con un join.

Segundo, la consulta en la variable @sql puede fallar durante la ejecución del buclé si pasa alguna de las siguientes condiciones :
1. BDs en modo de compatibilidad que no soportan los las tablas con las que estás haciendo la consulta
2. i la colación entre las bases de datos es distinta en los campos nombre_database, usuario y rol

un saludo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 15
Ha aumentado su posición en 45 puestos en SQL (en relación al último mes)
Gráfica de SQL

reporte sql server

Publicado por Juan (6 intervenciones) el 15/03/2019 16:04:02
Sin-titulo
quiero que mi reporte salga de esta manera ya que con la sentencia pivot condicionaria las filas de los roles y las convertiria en columnas
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Vega
Val: 54
Ha aumentado su posición en 8 puestos en SQL (en relación al último mes)
Gráfica de SQL

reporte sql server

Publicado por Vega (22 intervenciones) el 18/03/2019 11:55:00
Hola
A ver, te comento. Lo que pides es algo complicado de explicar así que prefiero dejar unos comentarios en el código que te adjunto.
He intentado reusar los nombres de tus variables lo más posible, aunque he creado unas cuantas más para solucionar tu problema.
Soy consciente de que no es la solución más elegante y eficiente, pero creo que produce el resultado deseado.
También estoy demostrando como evitar el uso de cursores con éste ejemplo aunque entre el pivot y la doble ejecución de la consulta a sys.databases, se nota que tarda algo más de lo deseado en devolver el resultado.

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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)
Aaa!

y cuidado con la sentencia en la línea 70 sobre la version de sql.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Revisar política de publicidad