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

Nota:

Los roles integrados 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.

SE APLICA A: Azure SQL Database

En Azure SQL Database, el servidor es un concepto lógico y no se pueden conceder permisos en un 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## contiene 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 tres 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 de nivel de servidor integrados

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

Rol de nivel de servidor integrado 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. Este 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. Se recomienda 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. Se recomienda 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 tiene el 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_ServerStateReader## Los miembros del rol fijo de servidor ##MS_ServerStateReader## pueden leer todas las vistas de administración dinámica (DMV) y las 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.
##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 FREESYSTEMCACHE ('ALL') y DBCC SQLPERF();

Permisos de roles fijos de servidor

Cada rol integrado de nivel de servidor tiene determinados 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 heredados 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_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##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

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 (Transact-SQL) Metadatos Indica si un inicio de sesión de SQL es miembro del rol de nivel de servidor especificado.
sys.server_role_members (Transact-SQL) Metadatos Devuelve una fila por cada miembro de cada rol de nivel de servidor.
sys.sql_logins (Transact-SQL) Metadatos Devuelve una fila por cada inicio de sesión de SQL.
ALTER SERVER ROLE (Transact-SQL) 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 ejemplo siguiente se agrega el inicio de sesión de SQL "Jiao" al rol de nivel de 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  

Este es el conjunto de resultados.

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

Ejecute este comando desde una base de datos de usuario.

-- Creating 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 Azure AD inicios de sesión

Ejecute este comando en la base de datos virtual master para ver todos Azure AD inicios de sesión que forman parte de roles de nivel de servidor en SQL Database. Para obtener más información sobre Azure AD inicios de sesión de servidor, vea Azure Active Directory entidades de seguridad de servidor.

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       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 members 
       ON server_role_members.member_principal_id = members.principal_id;

E. Comprobación de los roles de base de datos maestra virtual 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 OUTER JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id LEFT OUTER 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 Azure AD 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.

Consulte también