Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to know the last login date of a user

Post Views 45,511 views
Esse post é a parte 15 de 21 da série Security and Auditing
Reading time 7 minutes

Hey guys!
Nesse post, eu gostaria de trazer uma solução para vocês de como saber a data do último login de um usuário no SQL Server, que é uma informação que frequentemente vejo alguém querendo saber como obter, especialmente para identificar os usuários que acessam o ambiente ou não, para fazer mapeamentos de dados e/ou excluir usuários que não estão acessando o banco mais.

Vou demonstrar algumas soluções que podem atender essa necessidade, mas nenhuma delas é algo nativo do SQL Server criado especificamente com esse intuito, como uma simples coluna de last_login numa view como a sys.server_principals, por exemplo.

Solução #1 – Coluna accdate na sys.syslogins (NÃO resolve)

“Solução” bem comum de encontrar em blogs e fóruns para resolver esse tipo de problema, essa consulta NÃO retorna a data do último logon de um usuário, como muitas pessoas acreditam. A view de catálogo sys.syslogins está marcada como “deprecated” desde o SQL Server 2005 e desde o SQL 2008, pelo menos, podemos identificar no código-fonte dessa view, que a coluna “accdate” tem sempre o mesmo valor da coluna “createdate”

Solução #2 – MAX(login_time) na sys.dm_exec_sessions (NÃO resolve)

Mais uma “solução” totalmente errada que eu vejo em muitos sites, blogs e fóruns, a utilização da DMV sys.dm_exec_sessions JAMAIS poderia ser usada para descobrir a data do último login de um usuário, pois essa DMV só mostra as informações das sessões ativas da instância.

Se a instância for reiniciada ou a sessão terminar de executar, por exemplo, essa informação já será perdida. Se quando a sua rotina de coleta dos dados de último login for executada e não tiver nenhuma sessão ativa mais para esse usuário, você também não terá essa informação disponível.

Por esses motivos, não considero essa consulta como uma solução para esse problema.

Solução #3 – Utilizando Login Auditing

Iniciando agora com soluções que de fato, funcionem, essa solução que vou apresentar consiste em ativar a auditoria de logins com sucesso também (o padrão é auditar somente falhas) e usar o errorlog para identificar as datas de logins dos usuários.

O ponto negativo dessa solução, é a quantidade de novos registros que vão passar a aparecer no errorlog. Além de consumir mais espaço, pode deixar mais difícil o troubleshooting de problemas utilizando o errorlog, devido à quantidade muito alta de linhas, dependendo da quantidade de logins da instância.

Como habilitar essa opção utilizando o SSMS:

Como habilitar essa opção utilizando o T-SQL:

Script utilizado para monitorar coletar os dados:

Result:

Com esse script acima, basta você criar um job para executá-lo periodicamente e coletar os dados necessários. A tabela dbo.LastLogin terá os dados mais atuais sobre o último login de cada uusário.

Solução #4 – Utilizando uma trigger de logon

Uma outra forma de você conseguir criar esse histórico de logins, é utilizando triggers de logon para isso. Essa é uma solução onde você tem mais controle do que utilizando o recurso de Login Auditing, mas ao mesmo tempo, dá um pouco mais de trabalho para criar.

O ponto negativo dessa solução, é que assim como toda trigger de logon, caso você crie algo errado no desenvolvimento da trigger ou o usuário que for logar não tenha permissão na tabela de histórico, isso irá IMPEDIR que ele consiga logar no banco. Então tenha muito cuidado com isso, e teste bastante antes de aplicar em produção.

Para saber mais sobre triggers de logon, dê uma lida no meu artigo SQL Server - How to implement login auditing and control (Logon Trigger).

Criação das tabelas de histórico

Criação da Trigger

Gera a análise do último login de cada usuário

Result:

Solução #5 – Utilizando Server Audit

Outra possível solução, é utilizar o recurso de Server Audit do SQL Server, o qual eu já demonstrei aqui no blog como utilizar através do artigo Auditing in SQL Server (Server Audit).

Nessa solução, que é bem leve do ponto de vista de consumo de recursos, eu vou criar um server audit e um server audit specification, e monitorar as ocorrências do evento SUCCESSFUL_LOGIN_GROUP. Irei inserir os dados retornados na tabela de histórico e depois analisar os resultados obtidos.

Criação das tabelas de histórico

Criação da Server Audit

Criação da Server Audit Specification

Gera a análise do último login de cada usuário

Result:

ATTENTION: A coluna “application_name” está disponível apenas a partir do SQL Server 2017. Caso você esteja utilizando uma versão anterior, remova essa coluna do insert no código anterior.

Bom pessoal, é isso! Espero que vocês tenham gostado dessas soluções que propus nesse artigo e que elas possam ajudar no seu dia a dia.

Tem outras formas de monitorar isso? Deixa aqui nos comentários
Um grande abraço e até mais!