Identificar usuários e grupos no SQL SERVER

Para criar usuários, clique aqui para saber mais.

🛠️Para identificar todos os usuários criados no banco de dados atual:

SELECT name

FROM sys.database_principals

WHERE type IN ('S', 'U', 'G')

AND name NOT LIKE '##%'

ORDER BY name;

-- S = SQL user, U = Windows user, G = Windows group

-- NOT LIKE '##%' Ignora usuários internos do sistema

🛠️ Detalhamento de usuários, grupos e permissões

SELECT princ.name AS UserName,

       princ.type_desc AS UserType,

       roleprinc.name AS RoleName,

       perm.permission_name AS PermissionType

FROM sys.database_principals AS princ

LEFT JOIN sys.database_role_members AS members ON princ.principal_id = members.member_principal_id

LEFT JOIN sys.database_principals AS roleprinc ON members.role_principal_id = roleprinc.principal_id

LEFT JOIN sys.database_permissions AS perm ON perm.grantee_principal_id = princ.principal_id

WHERE princ.type IN ('S', 'U', 'G')

    AND princ.name NOT LIKE '##%'

ORDER BY princ.name;

A coluna UserType indica o tipo de usuário.

No exemplo acima, SQL_SERVER significa que é um usuário do SQL SERVER. Outros valores possíveis seriam WINDOWS_USER para usuários do Windows autenticados e WINDOWS_GROUP para grupos do Windows autenticados.

A coluna RoleName indica o nome do grupo ao qual o usuário pertence.

A coluna PermissionType indica o tipo de permissão que o usuário tem. O valor CONNECT indica que o usuário tem permissão para se conectar ao banco de dados. Outros tipos de permissões poderiam ser SELECT, INSERT, UPDATE, DELETE, etc.

🛠️Identificar grupos existentes relacionados ao gerenciamento de privilégios

Roles (ou funções) são grupos de permissões. Em vez de dar permissões diretamente a cada usuário, você adiciona usuários a roles que já têm as permissões necessárias.

SELECT name, type_desc 

FROM sys.database_principals 

WHERE type = 'R' -- R = database role

ORDER BY name;

🛠️Gerenciando roles

No SQL Server, roles (ou funções) funcionam como grupos de permissões. Em vez de conceder permissões individualmente a cada usuário, você cria uma role e concede as permissões desejadas a essa role. Depois, basta adicionar os usuários à role, e todos herdarão as permissões automaticamente.

-- 1. Criar role personalizada

CREATE ROLE LeituraDados;

-- 2. Conceder permissão

GRANT SELECT ON SCHEMA :: dbo TO LeituraDados;

-- Permissão de leitura (SELECT)

GRANT SELECT ON SCHEMA::dbo TO grupo_leitura_escrita;

-- Permissões de escrita (INSERT, UPDATE, DELETE)

GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO grupo_leitura_escrita;

-- 3. Criar usuário

CREATE USER amanda FOR LOGIN amanda_login;

-- 4. Associar à role

ALTER ROLE LeituraDados ADD MEMBER amanda;

-- 5. Verificar quem está na role

SELECT rolep.name AS RoleName, memberp.name AS UserName

FROM sys.database_role_members m

JOIN sys.database_principals rolep ON m.role_principal_id = rolep.principal_id

JOIN sys.database_principals memberp ON m.member_principal_id = memberp.principal_id

WHERE rolep.name = 'grupo_leitura_escrita';

-- 6. Remover usuário

ALTER ROLE grupo_leitura_escrita DROP MEMBER amanda;

-- 7. Excluir uma role

Só é possível excluir uma role depois de remover todos os membros e revogar ou remover as permissões concedidas

DROP ROLE grupo_leitura_escrita;

Grupos padrões no SQL Server

1.      public: Todos os usuários fazem parte do grupo "public" por padrão. É um grupo especial que contém todas as permissões básicas que todos os usuários devem ter no banco de dados. Os usuários herdam as permissões do grupo "public" automaticamente.

2.      db_owner: Os membros deste grupo têm controle total sobre o banco de dados. Eles podem executar qualquer operação no banco de dados, incluindo a alteração de sua estrutura, adição/remoção de usuários, etc.

3.      db_accessadmin: Os membros deste grupo podem adicionar ou remover logons de usuários de banco de dados.

4.      db_securityadmin: Os membros deste grupo podem gerenciar as permissões de segurança no banco de dados, como conceder ou revogar permissões de usuário.

5.      db_ddladmin: Os membros deste grupo podem executar operações DDL (Data Definition Language), como criar, modificar ou excluir objetos de banco de dados.

6.      db_backupoperator: Os membros deste grupo podem fazer backups ou restaurações do banco de dados.

7.      db_datareader: Os membros deste grupo podem ler (selecionar) todos os dados em todas as tabelas do banco de dados.

8.      db_datawriter: Os membros deste grupo podem modificar (inserir, atualizar e excluir) todos os dados em todas as tabelas do banco de dados.

9.      db_denydatareader: Os membros deste grupo são explicitamente negados de ler dados em todas as tabelas do banco de dados. Essa negação pode ser útil quando você deseja negar permissões a um grupo específico.

10.  db_denydatawriter: Os membros deste grupo são explicitamente negados de modificar dados em todas as tabelas do banco de dados. Mais uma vez, essa negação pode ser útil quando você deseja negar permissões a um grupo específico.