Roles del servidor de Azure SQL Database para la administración de permisos

  • Artículo

Se aplica a:Azure SQL Database

En este artículo se describen los roles fijos de nivel de servidor en Azure SQL Database.

Nota:

Los roles fijos de nivel de servidor que aparecen en este artículo se encuentran en versión preliminar pública para Azure SQL Database. Estos roles de nivel de servidor también forman parte de la versión de SQL Server 2022.

Información general

En Azure SQL Database, el servidor es un concepto lógico y no se pueden conceder permisos en el nivel de servidor. Para simplificar la administración de permisos, Azure SQL Database proporciona un conjunto de roles fijos de nivel de servidor para ayudarle a administrar los permisos en un servidor lógico. Los roles son entidades de seguridad que agrupan inicios de sesión.

Nota:

En este artículo, el concepto de roles es similar al de los grupos del sistema operativo Windows.

Estos roles fijos de nivel de servidor especiales usan el prefijo ##MS_ y el sufijo ## para distinguirse de otras entidades de seguridad normales creadas por el usuario.

Como sucede con las instancias locales de SQL Server, los permisos de servidor se organizan jerárquicamente. Los permisos que mantienen estos roles de nivel de servidor se pueden propagar a los permisos de base de datos. Para que los permisos sean útiles de forma eficaz al nivel de base de datos, un inicio de sesión debe ser miembro del rol de nivel de servidor ##MS_DatabaseConnector##, que concede CONNECT en todas las bases de datos o tener una cuenta de usuario en bases de datos individuales. Esto también se aplica a la base de datos virtual master.

Por ejemplo, el rol de nivel de servidor ##MS_ServerStateReader## tiene el permiso VIEW SERVER STATE. Si un inicio de sesión que es miembro de este rol tiene una cuenta de usuario en las bases de datos master y WideWorldImporters, este usuario tendrá el permiso VIEW DATABASE STATE en esas dos bases de datos.

Nota:

Cualquier permiso se puede denegar dentro de las bases de datos de usuario, lo que invalida la concesión en todo el servidor mediante la pertenencia a roles. Pero en la base de datos del sistema maestra, no se pueden conceder ni denegar permisos.

Azure SQL Database proporciona actualmente siete roles fijos de servidor. Los permisos que se conceden a los roles fijos de servidor no se pueden cambiar y no pueden tener otros roles fijos como miembros. Puede agregar inicios de sesión SQL de nivel servidor como miembros a roles de nivel de servidor.

Importante

Cada miembro de un rol fijo de servidor puede agregar otros inicios de sesión a ese mismo rol.

Para obtener más información sobre los inicios de sesión y los usuarios de Azure SQL Database, vea Autorización del acceso de base de datos a SQL Database, Instancia administrada de SQL y Azure Synapse Analytics.

Roles fijos de nivel de servidor

En la tabla siguiente se muestran los roles fijos de nivel de servidor y sus capacidades.

Rol fijo de nivel de servidor Descripción
##MS_DatabaseConnector## Los miembros del rol fijo de servidor ##MS_DatabaseConnector## pueden conectarse a cualquier base de datos sin necesidad de que una cuenta de usuario de la base de datos se conecte.

Para denegar el permiso CONNECT a una base de datos específica, los usuarios pueden crear una cuenta de usuario coincidente para este inicio de sesión en la base de datos y, a continuación, DENY al permiso CONNECT al usuario de base de datos. El permiso DENY anulará el permiso GRANT CONNECT procedente de este rol.
##MS_DatabaseManager## Los miembros del rol fijo de servidor ##MS_DatabaseManager## pueden crear y eliminar bases de datos. Un miembro del rol ##MS_DatabaseManager## que crea una base de datos se convierte en el propietario de dicha base de datos, lo que permite que el usuario se conecte a ella como el usuario dbo. El usuario dbo tiene todos los permisos de base de datos en la base de datos. Los miembros del rol ##MS_DatabaseManager## no necesariamente tienen permiso para acceder a las bases de datos que no son de su propiedad. Debe usar este rol de servidor a través del rol de nivel de base de datos dbmanager que existe en master.
##MS_DefinitionReader## Los miembros del rol fijo de servidor ##MS_DefinitionReader## pueden leer todas las vistas de catálogo cubiertas por VIEW ANY DEFINITION, respectivamente VIEW DEFINITION en cualquier base de datos en la que el miembro de este rol tenga una cuenta de usuario.
##MS_LoginManager## Los miembros del rol fijo de servidor ##MS_LoginManager## pueden crear y eliminar inicios de sesión. Debe usar este rol de servidor a través del rol de nivel de base de datos loginmanager que existe en master.
##MS_SecurityDefinitionReader## Los miembros del rol fijo de servidor ##MS_SecurityDefinitionReader## pueden leer todas las vistas de catálogo cubiertas por VIEW ANY SECURITY DEFINITION, y, respectivamente, tienen permiso VIEW SECURITY DEFINITION en cualquier base de datos en la que el miembro de este rol tenga una cuenta de usuario. Se trata de un pequeño subconjunto de a qué tiene acceso el rol de servidor ##MS_DefinitionReader##.
##MS_ServerStateManager## Los miembros del rol fijo de servidor ##MS_ServerStateManager## tienen los mismos permisos que el rol ##MS_ServerStateReader##. Además, contiene el permiso ALTER SERVER STATE, que permite el acceso a varias operaciones de administración, como DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL') y DBCC SQLPERF().
##MS_ServerStateReader## Los miembros del rol fijo de servidor ##MS_ServerStateReader## pueden leer todas las vistas de administración dinámica (DMV) y funciones cubiertas por VIEW SERVER STATE, respectivamente, VIEW DATABASE STATE en cualquier base de datos en la que el miembro de este rol tenga una cuenta de usuario.

Permisos de roles fijos de servidor

Cada rol fijo de nivel de servidor cuenta con diversos permisos asignados. En la tabla siguiente se muestran los permisos asignados a los roles de nivel de servidor. También muestra los permisos de nivel de base de datos, que se heredan siempre que el usuario pueda conectarse a bases de datos individuales.

Rol fijo de nivel de servidor Permisos a nivel de servidor Permisos de nivel de base de datos (si existe un usuario de base de datos que coincida con el inicio de sesión)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, , VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN N/D
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Permisos

Solo la cuenta de administrador del servidor o la cuenta de administrador de Microsoft Entra (que puede ser un grupo de Microsoft Entra) pueden agregar o quitar otros inicios de sesión a roles de servidor o desde ellos. Esto es específico para Azure SQL Database.

Nota:

Microsoft Entra ID era conocido anteriormente como Azure Active Directory (Azure AD).

Trabajo con roles de nivel de servidor

En la tabla siguiente se explican las vistas del sistema y las funciones que se pueden utilizar para trabajar con roles de nivel de servidor en Azure SQL Database.

Característica Tipo Descripción
IS_SRVROLEMEMBER Metadatos Indica si un inicio de sesión de SQL es miembro del rol de nivel de servidor especificado.
sys.server_role_members Metadatos Devuelve una fila por cada miembro de cada rol de nivel de servidor.
sys.sql_logins Metadatos Devuelve una fila por cada inicio de sesión de SQL.
ALTER SERVER ROLE Get-Help Cambia la pertenencia de un rol del servidor.

Ejemplos

En los ejemplos de esta sección se muestra cómo trabajar con roles de nivel de servidor en Azure SQL Database.

A Adición de un inicio de sesión de SQL a un rol de nivel de servidor

En el siguiente ejemplo se añade el inicio de sesión de SQL Jiao al rol de nivel servidor ##MS_ServerStateReader##. Esta instrucción se debe ejecutar en la base de datos virtual master.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Enumeración de todas las entidades de seguridad (autenticación SQL) que son miembros de un rol de nivel de servidor

La instrucción siguiente devuelve todos los miembros de cualquier rol fijo de nivel de servidor mediante las vistas de catálogo sys.server_role_members y sys.sql_logins. Esta instrucción se debe ejecutar en la base de datos virtual master.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Ejemplo completo: Adición de un inicio de sesión a un rol de nivel de servidor, recuperación de metadatos para la pertenencia a roles y permisos, y ejecución de una consulta de prueba

Parte 1: Preparación de la pertenencia a roles y la cuenta de usuario

Ejecute este comando desde la base de datos virtual master.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

El conjunto de resultados es el siguiente:

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Ejecute este comando desde una base de datos de usuario.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Parte 2: Prueba de la pertenencia a roles

Inicie sesión como Jiao y conéctese a la base de datos de usuario utilizada en el ejemplo.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Comprobación de los roles de nivel de servidor para inicios de sesión de Microsoft Entra

Ejecute este comando en la base de datos virtual master para ver todos los inicios de sesión de Microsoft Entra que forman parte de roles de nivel de servidor en SQL Database. Para más información sobre los inicios de sesión de Microsoft Entra, vea Entidades de seguridad de servidor de Microsoft Entra.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Comprobación de los roles de base de datos virtual master para inicios de sesión específicos

Ejecute este comando en la base de datos virtual master para comprobar los roles bob que tiene o cambie el valor para que coincida con la entidad de seguridad.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Limitaciones de los roles de nivel de servidor

  • Las asignaciones de roles pueden tardar hasta cinco minutos en surtir efecto. Además, para las sesiones existentes, los cambios en las asignaciones de roles del servidor no tienen efecto hasta que la conexión se cierra y se vuelve a abrir. Esto se debe a la arquitectura distribuida entre la base de datos master y otras bases de datos en el mismo servidor lógico.

    • Solución alternativa parcial: para reducir el período de espera y asegurarse de que las asignaciones de roles del servidor están actualizadas en una base de datos, un administrador del servidor o un administrador de Microsoft Entra puede ejecutar DBCC FLUSHAUTHCACHE en las bases de datos de usuario a las que tiene acceso el inicio de sesión. Los usuarios que han iniciado sesión actualmente tienen que volver a conectarse después de ejecutar DBCC FLUSHAUTHCACHE para que los cambios de pertenencia surtan efecto en ellos.

  • IS_SRVROLEMEMBER() no se admite en la base de datos master.

Contenido relacionado