Seja você um administrador de banco de dados ou apenas alguém responsável por gerenciar o SQL Server, é importante saber quem tem acesso a quais informações dentro de seus bancos de dados. Realizar uma auditoria de permissões pode ajudar a identificar quaisquer riscos de segurança potenciais ou garantir a conformidade com regulamentações. Neste artigo, discutiremos uma maneira rápida e eficiente de realizar uma auditoria de permissões no SQL Server.

O primeiro passo no processo de auditoria é identificar os logins (principais do servidor) e usuários (principais do banco de dados) que precisam ser auditados. Você pode criar uma lista delimitada por vírgulas de logins e usuários que deseja verificar. Por exemplo:

DECLARE @UsersorGroups VARCHAR(1024) = 'Phantom, Gargouille, TestUser'

Em seguida, você precisa especificar os bancos de dados que deseja incluir na auditoria. Novamente, você pode criar uma lista delimitada por vírgulas de bancos de dados. Por exemplo:

DECLARE @DBList VARCHAR(1000) = 'master,TestDB,TrainingDB, AdventureWorks2014'

Depois de definir os logins, usuários e bancos de dados a serem auditados, você pode usar o seguinte script para realizar a auditoria de permissões:

SET NOCOUNT ON;
GO

CREATE TABLE #Principals (UserorGroup sysname)  
INSERT INTO #Principals (UserorGroup)
     SELECT Item   
           FROM DBA.dbo.DelimitedSplit8K(@UsersorGroups,',')

CREATE TABLE #DBs (DBName sysname)  
INSERT INTO #DBs (DBName)
     SELECT Item   
           FROM DBA.dbo.DelimitedSplit8K(@DBList,',')

CREATE TABLE #PermsAudit (DBName sysname,ServerRole VARCHAR(128), PrincipalName sysname,PrincipalPerms VARCHAR(128),ServerRolePermissions VARCHAR(2048),DBRolePermissions VARCHAR(2048))

DECLARE getperms CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT d.name
FROM sys.databases d
INNER JOIN #DBs td
ON d.name = td.DBName;

OPEN getperms;
FETCH NEXT FROM getperms INTO @DBToQuery;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ' + @DBtoQuery +';' + char(10) +
'SELECT DB_NAME() AS DBName,roleprin.name AS ServerRole, pr.name AS PrincipalName, sp.permission_name AS PrincipalPerms
,(SELECT 
STUFF( 
(SELECT '', <'' + sp.permission_name + ''>'' 
FROM sys.server_permissions sp
WHERE roleprin.principal_id = sp.grantee_principal_id
ORDER BY sp.permission_name 
FOR XML PATH(''''), type 
).value(''(./text())[1]'',''varchar(max)'') 
, 1, 2, '''') ) AS ServerRolePermissions
,(SELECT 
STUFF( 
(SELECT '', <'' + dpr.permission_name + ''>'' 
FROM sys.database_principals dp
INNER JOIN sys.database_permissions dpr
ON dp.principal_id = dpr.grantee_principal_id
WHERE dp.sid = pr.sid
ORDER BY dpr.permission_name 
FOR XML PATH(''''), type 
).value(''(./text())[1]'',''varchar(max)'') 
, 1, 2, '''') ) AS DBRolePermissions
FROM sys.server_principals pr
LEFT OUTER JOIN sys.server_role_members srm
ON pr.principal_id = srm.member_principal_id
INNER JOIN sys.server_permissions sp
ON sp.grantee_principal_id = pr.principal_id
LEFT OUTER JOIN sys.server_principals roleprin
ON srm.role_principal_id = roleprin.principal_id
INNER JOIN #Principals tp
ON pr.name = tp.UserorGroup';

INSERT INTO #PermsAudit
        ( DBName ,
          ServerRole ,
          PrincipalName ,
          PrincipalPerms ,
          ServerRolePermissions ,
          DBRolePermissions
        )
EXECUTE (@SQL);

FETCH NEXT FROM getperms INTO @DBToQuery;
END

CLOSE getperms;
DEALLOCATE getperms;

SELECT *
FROM #PermsAudit;

DROP TABLE #Principals;
DROP TABLE #DBs;
DROP TABLE #PermsAudit;
GO

Vamos analisar o script e entender o que ele faz:

• Primeiro, criamos duas tabelas temporárias para armazenar os logins e bancos de dados a serem auditados.
• Em seguida, usamos um cursor para iterar por cada banco de dados e executar uma consulta SQL dinâmica para recuperar as permissões dos logins e usuários especificados.
• A consulta usa subconsultas e FOR XML para construir strings delimitadas de permissões de função do servidor e permissões de função do banco de dados para cada login ou usuário.
• Os resultados da auditoria de permissões são armazenados em outra tabela temporária.
• Por fim, recuperamos os resultados da tabela temporária e os exibimos.

Usando esse script, você pode identificar rapidamente as permissões atribuídas aos logins e usuários especificados em cada banco de dados. Essas informações podem ajudá-lo a garantir que as pessoas certas tenham o nível adequado de acesso aos seus bancos de dados.

Lembre-se de modificar o script com seus próprios logins, usuários e bancos de dados antes de executá-lo. Os resultados variarão dependendo da sua configuração específica.

Realizar auditorias regulares de permissões é uma parte essencial para manter a segurança e integridade do seu ambiente SQL Server. Isso permite que você identifique quaisquer vulnerabilidades de segurança potenciais e tome as medidas apropriadas para mitigá-las.

Para mais artigos sobre auditorias e auditoria, confira nosso blog para recursos adicionais.