Obtendo informações sobre Permissões de Logins e Usuários no Microsoft SQL Server 2008 e R2.

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:

1. Script – User Role List and Login Type;
2. Script – Database Login and User Role List; e
3. 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.