Galera, boa tarde, tudo em paz?
Estive um pouco ausente nos últimos dias devido aos meus compromissos profissionais e acadêmicos, o que acabou impossibilitando manter o mesmo ritmo de posts em meu blog. Mesmo assim, consegui um tempinho para elaborar mais este artigo, que a meu ver poderá ajudar muito.
Então vamos lá, espero que você goste.
Pois bem, acredito que normalmente em suas atividades diárias você deve ter se deparado com a necessidade de identificar permissões relacionadas:
- Database Roles;
- Logins;
- Usuários; e
- Server Roles.
Pensando justamente neste tipo de necessidade, coletei alguns Scripts que utilizo para compor a estrutura deste artigo, a fim de ilustrar e demonstrar como podemos através de linha de comando no Management Studio encontrar valiosas informações.
Antes de começar a demonstrar como estes Scripts podem ser utilizados e quais suas respectivas funcionalidades, quero destacar a relação de Catalog Views que iremos trabalhar.
Segue abaixo a relação de Catalog Views:
- sys.database_permissions: Retorna uma linha para cada permissão ou permissão de exceção de coluna no banco de dados. Para colunas, há uma linha para cada permissão que é diferente da permissão de nível de objeto correspondente.
- sys.database_principals: Retorna uma linha com o conjunto de permissões para cada Banco de Dados existente na Instância SQL Server.
- sys.database_role_members: Retorna uma linha para cada membro existente nas Database Roles vinculadas aos Bancos de Dados existentes na Instância SQL Server.
- sys.schemas: Apresenta uma linha para cada Database Schema vinculadas aos Bancos de Dados existentes na Instância SQL Server.
- sys.server_principals: Contém uma linha para cada entidade (Usuário do Windows, Login SQL, Grupo de Usuário do Windows, Server Role, etc) de nível de servidor.
- sys.server_role_members: Apresenta uma linha para cada membro existente nas Server Roles vinculadas ao Servidor ou Instância SQL Server.
- sys.syslogins: Contém uma linha para cada conta de Login existente no Servidor ou Instância SQL Server.
- sys.sysobjects: Contém uma linha para cada objeto existente em Banco de Dados, como: Constraints, Rule, Stored Procedures, etc.
Observações:
Importante destacar que todas estas Catalog Views, foram introduzidas a partir da versão do Microsoft SLQ Server 2005, com base, nas antigas System Views existentes no Microsoft SQL Server 2000.
Além disso, algumas destas Catalog Views, como por exemplo: Sys.Server_Principals requer um nível de permissão mais elevado dentro da Instância SQL Server que você se encontra conectado.
Muito bem, agora que já sabemos os objetos que vamos utilizar como mecanismos para obtenção das informações que necessitamos, vamos começar a trabalhar um pouco com os Scripts.
Para este artigo separei três Scripts denominados:
- Script – User Role List and Login Type;
- Script – Database Login and User Role List; e
- Script – User Role Permissions – Grantor, Object Permissions and Permissions Type.
Vou começar seguindo a seqüência que destaquei acima, apresentando o Script que lista ás Roles de Usuários e Tipos de Logins, vinculados a instância ou servidor SQL Server que você esta conectado.
1 – Script – User Role List and Login Type:
SELECT
CASE
WHEN SSPs2.name IS NULL THEN ‘Public’
ELSE SSPs2.name
END AS ‘Role Name’,
SSPs.name AS ‘Login Name’,
Case SSPs.is_disabled
When 0 Then ‘0 – Habilitado’
When 1 Then ‘1 – Desabilitado’
End AS ‘Login Status’,
SSPs.type_desc AS ‘Login Type’
FROM sys.server_principals SSPs LEFT JOIN sys.server_role_members SSRM
ON SSPs.principal_id = SSRM.member_principal_id
LEFT JOIN sys.server_principals SSPs2
ON SSRM.role_principal_id = SSPs2.principal_id
WHERE SSPs2.name IS NOT NULL
OR SSPs.type_desc <> ‘CERTIFICATE_MAPPED_LOGIN’
AND SSPs.type_desc <> ‘SERVER_ROLE’
AND SSPs2.name IS NULL
ORDER BY SSPs2.name DESC, SSPs.name
Vale ressaltar que o ponto principal deste código, encontra-se na Junção entre as Catalog Views apresentando acima, onde estamos, realizando a junção, entre a Sys.Server_Principals e Sys.Server_Role_Members, tendo como condição as Colunas Principal_ID e Member_Principal_ID respectivamente.
Além disso, outro fator importante relaciona-se a cláusula Where, responsável em realizar o filtro de dados, através da coluna Type_Desc pertencente a Sys.Server_Principals, garantindo assim o retorno das principals relacionadas somente aos: Logins, Server Roles, Windows Group e Windows Login. Sendo assim, após executar o Script, temos o resultado apresentado abaixo pela Figura 1:
Figura 1 – Relação de Roles, Logins, Status e Tipos de Logins.
Agora, vamos trabalhar com o Script – Database Login and User Role List, responsável em apresentar a relação de Roles, Login e Usuário, vinculados ao Banco de Dados ao qual você esta executando este Script.
2. Script – Database Login and User Role List:
With Roles (Role, Login, [User])
As
(SELECT SDPs2.name AS Role,
SDPs1.name AS [User],
SL.name AS Login
FROM [Master].sys.database_principals SDPs1
Inner JOIN [Master].sys.syslogins SL
ON SDPs1.sid = SL.sid
Inner JOIN [Master].sys.database_role_members SRM
ON SRM.member_principal_id = SDPs1.principal_id
Inner JOIN [Master].sys.database_principals p2
ON SRM.role_principal_id = SDPs2.principal_id
AND SDPs2.type IN (‘R’)
WHERE SDPs1.type IN (‘S’,’U’,’G’))
Select * from Roles
ORDER BY Role, Login
Se compararmos o tamanho deste segundo script em relação ao primeiro, podemos observar que o nível de complexidade é maior, devido à quantidade de junções realizadas, entre as Catalog Views:
- Sys.Database_Principals e Sys.SysLogin;
- Sys.Database_Principals e Sys.Database_Role_Members; e
- Sys.Database_Role_Members e Sys.Database_Principals.
Outro detalhe importante no último Join está sendo utilizado o operador And na coluna Type forçando o filtro de dados, retornando somente a Database Role. Em contra partida a cláusula Where declarada abaixo esta filtrando as Databases Roles do Tipo: SQL User, Windows User e Windows Group.
Além disso, podemos também notar a utilização de uma CTE (Common Table Expression) como um mecanismo que poderá nos possibilitar a reutilização do código de forma mais elegante.
Ao realizarmos a execução deste teremos um resultado similar ao apresentado pela Figura 2:
Figura 2 – Role, Login e Usuário.
Bem, estamos quase no final, esta faltando apresentar o último Script, denominado Script – User Role Permissions – Grantor, Object Permissions and Permissions Type. Neste último Script teremos o retorno do Nome do Usuário, Conjunto de Permissões, Permissões por Objeto, Grantor e o Tipo da Permissão.
3. Script – User Role Permissions – Grantor, Object Permissions and Permissions Type.
SELECT SDPs1.name AS [User],
SDBPs.permission_name AS [Permissions],
ISNULL(SDBPs.class_desc,”) COLLATE latin1_general_cs_as +
ISNULL(‘:’+SO.name,”) COLLATE latin1_general_cs_as + ISNULL(‘:’+SC.name,”) COLLATE latin1_general_cs_as As PermissionObjetct,
SDPs.name as Grantor,
SDBPs.state_desc AS PermissionType
FROM [Master].sys.database_permissions SDBPs
INNER JOIN [Master].sys.database_principals SDPs
on SDBPs.grantor_principal_id=SDPs.principal_id
INNER JOIN [Master].sys.database_principals SDPs1
on SDBPs.grantee_principal_id=SDPs1.principal_id
LEFT OUTER JOIN [Master].sys.sysobjects SO
on SDBPs.major_id=SO.id and SDBPs.class =1
LEFT OUTER JOIN [Master].sys.schemas SC
on SDBPs.major_id=SC.[schema_id]
WHERE SDPs1.name IN (‘public’)
And SDBPs.permission_name NOT IN(‘CONNECT’)
ORDER BY User, Permissions, PermissionObjetct
Boa parte das Catalog Views apresentadas como começo do artigo é utilizada neste terceiro script no processo de junção, onde podemos identificar as seguintes junções:
- Sys.Database_Permissions e Sys.Database_Principals;
- Sys.Database_Principals e Sys.Database_Principals;
- Sys.Database_Principals e Sys.SysObjects; e
- Sys.Database_Principals e Sys.Schemas.
Da mesma forma que os outros Scripts, também podemos notar a utilizar da cláusula Where e outros operadores lógicos e condicionais como mecanismos de filtragem de dados. Se observarmos na cláusula Where está sendo filtrada as permissões pelo nome do usuário, neste caso, o Public em conjunto com o nome da permissão neste tipo Connect.
Ao executarmos este Script poderemos receber um retorno similar ao apresentado na Figura 3:
Figura 3 – Relação de Usuários e suas Permissões.
Neste momento, chegamos ao final de mais um artigo. Espero que as informações e exemplos apresentados aqui possam te ajudar.
Fique a vontade para deixar seu comentário e compartilhar este material com seus contatos.
Mais uma vez obrigado.
Até mais.
Uma consideração sobre “Obtendo informações sobre Permissões de Logins e Usuários no Microsoft SQL Server 2008 e R2.”
Os comentários estão encerrados.