Cómo verificar los roles de usuario en SQL Server

Netwrix Auditor for SQL Server

1. Abra Netwrix Auditor y navegue a Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
2. Especifique los siguientes filtros:
   • En el filtro de User account , escriba el nombre completo del usuario (como MILKYWAY/TomSimpson).
   • En el filtro de Object type, elija Server Instance, Database.
3. Haga clic en Ver para crear un informe claro sobre los permisos efectivos del usuario.

Solución nativa

Listado de roles de SQL Server para un usuario

1. Inicie Microsoft SQL Server Management Studio (MSSMS).
2. En el menú File, haga clic en Connect Object Explorer.
3. En el cuadro de diálogo Connect to Server, especifique los siguientes ajustes:
   • En el cuadro de lista de Server type, seleccione Database Engine.
   • En el cuadro de texto Server name, escriba el nombre del servidor de clúster SQL.
   • En el cuadro de lista de Authentication seleccione su método de SQL Server Authenticationmethod y especifique las credenciales a utilizar. Si no desea volver a escribir la contraseña cada vez que se conecte al servidor, marque la opción Remember password.
4. Haga clic en Connect.
5. Haga clic en Execute (o presione la tecla F5 ).
6. Al conectarse, haga clic en New Query y pegue el siguiente script en el campo de consulta:

      select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• Revise la lista de roles a nivel de servidor y principios (nombres de miembros) en los resultados de la ejecución de la consulta:

Consultando roles de base de datos en SQL Server para un usuario

1. Inicie Microsoft SQL Server Management Studio (MSSMS).
2. En el menú File, haga clic en Connect Object Explorer.
3. En el cuadro de diálogo Connect to Server, especifique los siguientes ajustes:
   • En el cuadro de lista Server type, seleccione Database Engine.
   • En el cuadro de texto Server name, escriba el nombre del servidor de clúster SQL.
   • En el cuadro de lista de Authentication, elija su método de autenticación de SQL Server y especifique las credenciales a utilizar. Si no desea volver a escribir la contraseña cada vez que se conecte al servidor, marque la opción Remember password.
4. Haga clic en Connect.
5. Haz clic en Ejecutar (o presiona la tecla F5).
6. Al conectarse, seleccione la Database que necesita consultar para los roles de usuario.
7. Haga clic en Nueva consulta y pegue el siguiente script en el campo de consulta:

      SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• Revise la lista de roles a nivel de servidor y principios (nombres de miembros) en los resultados de la ejecución de la consulta:
  

Descubre cómo verificar los roles de usuario en SQL Server sin una sola consulta.

Microsoft SQL Server ofrece roles para ayudar a los administradores de bases de datos a gestionar permisos para datos estructurados. Los roles a nivel de servidor, como su nombre indica, otorgan acceso a todo el servidor, de manera similar a los grupos en el mundo de Windows. Cada base de datos SQL también puede tener sus permisos y roles únicos.

Para mantener la seguridad y cumplir con varias regulaciones, incluyendo PCI DSS y HIPAA, necesitas conocer todos los roles de servidor y base de datos asignados a cada usuario. Debido a la complejidad involucrada, es una tarea difícil si solo cuentas con herramientas nativas.

Para comenzar, la configuración a nivel de servidor, como los roles de servidor, permisos, credenciales de usuario y dependencias, se almacenan en la base de datos maestra. Utilizando la vista del sistema sys.server_principals.,

• Para listar usuarios y roles de servidor en SQL Server, puedes consultar vistas del sistema como sys.server_principals.
• Para listar usuarios y roles para bases de datos en SQL Server, puedes consultar vistas del sistema como sys.database_principals.

Aunque los procedimientos almacenados pueden ayudarte a gestionar áreas del servidor, tendrás que utilizar consultas para construir informes personalizados (por ejemplo, uno que coincida varias tablas por nombres de columnas específicos). Por ejemplo, la información de membresía de roles a nivel de servidor se almacena en la vista del sistema server_role_members de la base de datos master. Dado que los ID de los principales están vinculados, puedes obtener un resumen de los roles de usuario de SQL Server con una consulta uniendo sys.server_principals con master.sys.server_role_members basado en el número de ID. Aunque los usuarios pueden ver su membresía de rol de servidor y el ID principal de cada miembro de los roles fijos del servidor, recuerda que ver toda la membresía de roles de servidor requiere permisos adicionales o membresía en el rol fijo de servidor de security admin .

Para recopilar información a nivel de base de datos, debe consultar los roles de base de datos de SQL Server en cada base de datos individualmente, lo que puede llevar mucho tiempo. Además, los roles pueden estar anidados: los usuarios de la base de datos, los roles de aplicación y otros roles de base de datos pueden ser miembros de un rol de base de datos.

En resumen, obtener información comprensiva sobre los roles actuales de los usuarios con herramientas nativas puede ser complicado y extremadamente agotador. Con Netwrix Auditor, por otro lado, puedes obtener información detallada sobre quién tiene qué roles en servidores y bases de datos en un formato legible con solo unos clics. Los informes vinculados incluidos en el producto permiten a tus especialistas filtrar rápidamente las complicaciones de la membresía de roles de usuario anidados a través de un servidor entero, agilizando las investigaciones. Obtienes todos los detalles críticos que necesitas: una lista de cada objeto al que el usuario tiene acceso, con su ruta y tipo de objeto, los permisos que se otorgaron, cómo se otorgaron (directamente, a través de la membresía de roles, etc.), y si son explícitos o heredados. Puedes analizar los permisos desde diferentes ángulos, incluyendo los niveles de cuenta, objeto y servidor. Toda esta información se presenta y está disponible sin la molestia de escribir guiones, exportar información a Excel y tamizarla manualmente.