none
Acessos no SQL RRS feed

  • Pergunta

  • Bom dia a todos, existe algum comando ou log que eu consiga saber quais foram os usuários que acessaram o banco com data e hora?

    grato.

    segunda-feira, 22 de junho de 2020 14:16

Respostas

  • Olá amigão , 

    Vamos a outra possibilidade. 

    Já que você somente quer saber quem logou com data e hora . 

    Você pode ativar a Auditoria de Logon.

    Vá em: 

    Propriedades do Servidor /  Segurança  / Lá marque a opção: Logons com falha e bem sucedidos (ou somente bem sucedidos ai vai a seu critério) e confirme no botão OK. 

    Quando puder , faça o restart da instancia e sempre que precisa você poderá ir no log do próprio Sql SErver e ver as informações de quem logou com o nome do login com hora completa e até o HOST.

    Não coloquei os prints aqui do processo , mas, se tiver com dificuldades para entender o processo , informe aqui que gero os prints sem problemas. 


    Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    terça-feira, 23 de junho de 2020 16:15

Todas as Respostas

  • Dan_Simões,

    Sim, existem alguma possibilidades que podem se aproximar do que você deseja, não exatamente o que necessita.

    Veja se os exemplos abaixo te ajudam:

    -- Identificando o último acesso por Login --
    SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
    FROM sys.dm_exec_sessions
    GROUP BY login_name
    Go
    -- Identificando os últimos acessos por banco de dados --
    SELECT name [Banco de Dados], MAX(CAST(dt AS SMALLDATETIME)) [Último Acesso]
    FROM (
    	SELECT d.name, MAX(last_user_seek) DT1, MAX(last_user_scan) DT2
    		, MAX(last_user_lookup) DT3, MAX(last_user_update) DT4
    		, (SELECT create_date FROM sys.databases WHERE database_id = 2) DT5
    	FROM sys.dm_db_index_usage_stats v
    		RIGHT JOIN sys.databases d
    			ON v.database_id = d.database_id
    	WHERE d.database_id > 4
    	GROUP BY d.name
    	) P
    	UNPIVOT (dt FOR a IN (DT1, DT2, DT3, DT4, DT5)) AS UP
    GROUP BY name
    ORDER BY 2


    -- Identificando os últimos acessos de leitura e escrita por banco de dados --
    ;WITH myCTE AS
    (
    SELECT
      DB_NAME(database_id) AS TheDatabase,
      last_user_seek,
      last_user_scan,
      last_user_lookup,
      last_user_update
    FROM sys.dm_db_index_usage_stats
    )
    SELECT
      ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
      x.TheDatabase,
      MAX(x.last_read) AS  last_read,
      MAX(x.last_write) AS last_write
    FROM
    (
    SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
      UNION ALL
    SELECT TheDatabase,last_user_scan, NULL FROM myCTE
      UNION ALL
    SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
      UNION ALL
    SELECT TheDatabase,NULL, last_user_update FROM myCTE
    ) AS x
    
    GROUP BY TheDatabase
    ORDER BY TheDatabase


    Podemos também implementar recursos de Auditoria através do objeto Audit, ou utilizar os Extended Events.

    Complementando os exemplos anteriores, podemos fazer uso de Triggers relacionados a camada do Servidor, conhecidos como Triggers DDL, veja abaixo:

    USE master
    Go

    CREATE TABLE Ultimo_Login ( [LoginName] sysname primary key, Data_conexao datetime, Evento xml )
    Go CREATE TRIGGER [Monitora_Login] on ALL SERVER with execute as '____' after LOGON as begin declare @Evento XML, @LoginName sysname, @Data_conexao datetime; set @Evento= Eventdata(); set @LoginName= @Evento.value ('(/EVENT_INSTANCE/LoginName)[1]','sysname'); set @Data_conexao= @Evento.value ('(/EVENT_INSTANCE/PostTime)[1]','datetime'); IF exists (SELECT * from master..Ultimo_Login where [LoginName] = @LoginName) UPDATE master..Ultimo_Login set Data_conexao= @Data_conexao, Evento= @Evento where [LoginName] = @LoginName else INSERT into master..Ultimo_Login ([LoginName], Data_conexao, Evento) values (@LoginName, @Data_conexao, @Evento); End Go Declare @3meses datetime; Set @3meses= dateadd (month, -3, cast(current_timestamp as date)); SELECT P.name, P.type_desc, P.create_date, UL.Data_conexao from sys.server_principals as P left join master..Ultimo_Login as UL on UL.[LoginName] = P.name where P.is_disabled <> 1 and P.type in ('S', 'U') and P.create_date < @3meses and (UL.[LoginName] is null or UL.Data_conexao < @3meses) order by P.name;

    Observe que neste exemplo estamos fazendo uso dos Extended Events.

    Este link poderá lhe ajudar a entender um pouco mais sobre os Extended Events: https://voiceofthedba.com/2015/12/16/tracking-logins-with-extended-events/


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]



    segunda-feira, 22 de junho de 2020 14:33
    Moderador
  • Tem nao. Vc vai ter que criar sua estrutura de log junto com trigger de logon.
    • Sugerido como Resposta Avatar SQL segunda-feira, 29 de junho de 2020 11:06
    segunda-feira, 22 de junho de 2020 15:15
  • mas sys.dm_exec_sessions mostra usuario somente das conexoes ativas. aqueles dois scripts seguintes que postou nao mostra usuario. no normal sql server nao armazena registro de logon.

    • Editado Avatar SQL segunda-feira, 22 de junho de 2020 15:47
    segunda-feira, 22 de junho de 2020 15:19
  • Olá Dan,

    Isto sempre foi um problema no Sql Server. 

    Você poderá fazer uma trigger DDL conforme foi dito pelo amigo Junior Galvão.


    Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    terça-feira, 23 de junho de 2020 03:04
  • esse quarto script utiliza trigger de logon mas nao faz log das conexoes de usuarios.
    • Editado Avatar SQL terça-feira, 23 de junho de 2020 12:03
    terça-feira, 23 de junho de 2020 11:48
  • Muito obrigado Junior.
    terça-feira, 23 de junho de 2020 12:52
  • Obrigado Avatar SQL.
    terça-feira, 23 de junho de 2020 12:53
  • Olá amigão , 

    Vamos a outra possibilidade. 

    Já que você somente quer saber quem logou com data e hora . 

    Você pode ativar a Auditoria de Logon.

    Vá em: 

    Propriedades do Servidor /  Segurança  / Lá marque a opção: Logons com falha e bem sucedidos (ou somente bem sucedidos ai vai a seu critério) e confirme no botão OK. 

    Quando puder , faça o restart da instancia e sempre que precisa você poderá ir no log do próprio Sql SErver e ver as informações de quem logou com o nome do login com hora completa e até o HOST.

    Não coloquei os prints aqui do processo , mas, se tiver com dificuldades para entender o processo , informe aqui que gero os prints sem problemas. 


    Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    terça-feira, 23 de junho de 2020 16:15
  • Muito obrigado Junior.

    Dan_Simões,

    Estou a disposição.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 23 de junho de 2020 21:36
    Moderador
  • Isso vai fazer com q o log fique enooorme. melhor criar tabela separada.
    segunda-feira, 29 de junho de 2020 11:07