Partilhar via

Funções de servidor do Banco de Dados SQL do Azure para gerenciamento de permissões

  • Artigo

Aplica-se a: do Banco de Dados SQL do Azure

Este artigo descreve funções fixas de nível de servidor no Banco de Dados SQL do Azure.

Observação

As funções fixas de nível de servidor neste artigo estão em visualização pública para o Banco de Dados SQL do Azure. Essas funções de nível de servidor também fazem parte da versão para SQL Server 2022.

Visão geral

No Banco de Dados SQL do Azure, o servidor é um conceito lógico e as permissões não podem ser concedidas no nível do servidor. Para simplificar o gerenciamento de permissões, o Banco de Dados SQL do Azure fornece um conjunto de funções fixas no nível do servidor para ajudá-lo a gerenciar as permissões em um servidor lógico . As funções são entidades de segurança que agrupam logins.

Observação

O conceito de funções neste artigo é como grupos no sistema operativo Windows.

Essas funções fixas especiais ao nível do servidor utilizam o prefixo ##MS_ e o sufixo ## para se distinguirem de outras funções comuns criadas por utilizadores.

Como o SQL Server local, as permissões do servidor são organizadas hierarquicamente. As permissões mantidas por essas funções de nível de servidor podem se propagar para permissões de banco de dados. Para que as permissões sejam efetivamente úteis no nível do banco de dados, um login precisa ser membro da função de nível de servidor ##MS_DatabaseConnector##, que concede CONNECT a todos os bancos de dados, ou ter uma conta de usuário em cada banco de dados individual. Isso também se aplica ao banco de dados de master virtual.

Por exemplo, a função de nível de servidor ##MS_ServerStateReader## detém a permissão VIEW SERVER STATE. Se um login pertencente a esta função tiver uma conta de utilizador nas bases de dados master e WideWorldImporters, esse utilizador terá a permissão VIEW DATABASE STATE nessas duas bases de dados.

Observação

Qualquer permissão pode ser recusada em bancos de dados de usuários, efetivamente anulando a concessão a nível do servidor por meio da associação de função. No entanto, no banco de dados mestre do sistema , as permissões não podem ser concedidas ou negadas.

Atualmente, o Banco de Dados SQL do Azure fornece sete funções de servidor fixas. As permissões concedidas às funções de servidor fixas não podem ser alteradas e essas funções não podem ter outras funções fixas como membros. Você pode adicionar logons no nível do servidor como membros às funções no nível do servidor.

Importante

Cada membro de uma função de servidor fixa pode adicionar outros logins a essa mesma função.

Para obter mais informações sobre logons e usuários do Banco de Dados SQL do Azure, consulte Autorizar o acesso ao banco de dados ao Banco de Dados SQL, à Instância Gerenciada SQL e ao Azure Synapse Analytics.

Funções fixas no nível do servidor

A tabela a seguir mostra as funções fixas no nível do servidor e seus recursos.

Função fixa no nível do servidor Descrição
##MS_DatabaseConnector## Os membros da função de servidor fixa ##MS_DatabaseConnector## podem se conectar a qualquer banco de dados sem exigir uma conta de usuário no banco de dados para se conectar.

Para negar a permissão CONNECT a um banco de dados específico, os usuários podem criar uma conta de usuário correspondente para esse logon no banco de dados e, em seguida, DENY a permissão CONNECT para o usuário do banco de dados. Essa permissão DENY anula a permissão GRANT CONNECT proveniente dessa função.
##MS_DatabaseManager## Os membros da função de servidor fixa ##MS_DatabaseManager## podem criar e excluir bancos de dados. Um membro da função ##MS_DatabaseManager## que cria um banco de dados, torna-se o proprietário desse banco de dados, o que permite que o usuário se conecte a esse banco de dados como o usuário dbo. O usuário dbo tem todas as permissões de banco de dados no banco de dados. Os membros da função ##MS_DatabaseManager## não têm necessariamente permissão para acessar bancos de dados que não possuem. Você deve usar essa função de servidor sobre o dbmanager função de nível de banco de dados que existe no master.
##MS_DefinitionReader## Os membros da função de servidor fixa ##MS_DefinitionReader## podem ler todas as exibições de catálogo cobertas por VIEW ANY DEFINITION, respectivamente, VIEW DEFINITION em qualquer banco de dados no qual o membro dessa função tenha uma conta de usuário.
##MS_LoginManager## Os membros da função de servidor fixa ##MS_LoginManager## podem criar e excluir logons. Você deve usar esta função de servidor em vez da função de nível de banco de dados loginmanager que existe no master.
##MS_SecurityDefinitionReader## Os membros da função de servidor fixa ##MS_SecurityDefinitionReader## podem ler todas as vistas de catálogo cobertas pelo VIEW ANY SECURITY DEFINITIONe, respetivamente, têm permissão VIEW SECURITY DEFINITION em qualquer base de dados na qual o membro desta função tenha uma conta de utilizador. Este é um pequeno subconjunto do que a função de servidor ##MS_DefinitionReader## tem ao seu alcance.
##MS_ServerStateManager## Os membros da função de servidor fixa ##MS_ServerStateManager## têm as mesmas permissões que a função ##MS_ServerStateReader##. Além disso, possui a permissão ALTER SERVER STATE, que permite o acesso a diversas operações de gestão, tais como: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## Os membros da função de servidor fixa ##MS_ServerStateReader## podem ler todas as exibições de gerenciamento dinâmico (DMVs) e funções que são cobertas por VIEW SERVER STATEe VIEW DATABASE STATE, respetivamente, em qualquer base de dados na qual o membro dessa função tenha uma conta de utilizador.

Permissões das funções fixas de servidor

Cada função fixa de nível de servidor tem determinadas permissões atribuídas a ela. A tabela a seguir mostra as permissões atribuídas às funções no nível do servidor. Ele também mostra as permissões no nível de banco de dados, que são herdadas desde que o usuário possa se conectar a bancos de dados individuais.

Função fixa no nível do servidor Permissões no nível do servidor Permissões no nível do banco de dados (se existir um usuário do banco de dados correspondente ao logon)
##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/A
##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

Permissões

Somente a conta de administrador do servidor ou a conta de administrador do Microsoft Entra (que pode ser um grupo do Microsoft Entra) pode adicionar ou remover outros logons de ou para funções de servidor. Isso é específico do Banco de Dados SQL do Azure.

Observação

Microsoft Entra ID era anteriormente conhecido como Azure Ative Directory (Azure AD).

Trabalhar com funções de nível de servidor

A tabela a seguir explica as exibições do sistema e as funções que você pode usar para trabalhar com funções de nível de servidor no Banco de Dados SQL do Azure.

Funcionalidade Tipo Descrição
IS_SRVROLEMEMBER Metadados Indica se um login SQL é membro da função de nível de servidor especificada.
sys.server_role_members Metadados Retorna uma linha para cada membro de cada função de nível de servidor.
sys.sql_logins Metadados Retorna uma linha para cada logon SQL.
ALTERAR FUNÇÃO DE SERVIDOR Comando Altera a associação de uma função de servidor.

Exemplos

Os exemplos nesta seção mostram como trabalhar com funções de nível de servidor no Banco de Dados SQL do Azure.

Um. Adicionar um logon SQL a uma função no nível de servidor

O exemplo a seguir adiciona o login SQL Jiao para a função de nível de servidor ##MS_ServerStateReader##. Esta instrução deve ser executada no banco de dados de master virtual.

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

B. Listar todos os principais com autenticação SQL que são membros de uma função ao nível do servidor

A instrução a seguir retorna todos os membros de qualquer função fixa de nível de servidor usando as exibições de catálogo sys.server_role_members e sys.sql_logins. Esta instrução deve ser executada no banco de dados de master virtual.

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. Exemplo completo: adicione um logon a uma função no nível de servidor, recupere metadados para associação e permissões de função e execute uma consulta de teste

Parte 1: Preparando a associação de funções e a conta de utilizador

Execute este comando a partir do banco de dados de master virtual.

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

Aqui está o conjunto de resultados.

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

Execute este comando a partir de um banco de dados de usuário.

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

Parte 2: Testando a associação à função

Faça login como o utilizador Jiao e conecte-se ao banco de dados de usuários usado no exemplo.

-- 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. Verificar funções no nível de servidor para logins do Microsoft Entra

Execute este comando no banco de dados de master virtual para ver todos os logons do Microsoft Entra que fazem parte de funções de nível de servidor no Banco de dados SQL. Para mais informações sobre logins de servidor do Microsoft Entra, consulte Principais de servidor do 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. Verifique as funções do banco de dados virtual master para logins específicos

Execute este comando na base de dados virtual master para verificar os perfis que bob tem, ou altere o valor para se adequar ao seu principal.

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%';

Limitações das funções no nível do servidor

  • As atribuições de função podem levar até 5 minutos para entrar em vigor. Também para sessões existentes, as alterações nas atribuições de função de servidor não entram em vigor até que a conexão seja fechada e reaberta. Isso ocorre devido à arquitetura distribuída entre o banco de dados master e outros bancos de dados no mesmo servidor lógico.

    • Solução parcial: para reduzir o período de espera e garantir que as atribuições de função de servidor estejam atualizadas em um banco de dados, um administrador de servidor ou um administrador do Microsoft Entra pode executar DBCC FLUSHAUTHCACHE nos bancos de dados de usuários nos quais o logon tem acesso. Os usuários conectados atualmente ainda precisam se reconectar depois de executar DBCC FLUSHAUTHCACHE para que as alterações de associação entrem em vigor neles.

  • IS_SRVROLEMEMBER() não é suportado no banco de dados master.

Conteúdo relacionado