none
Estatísticas de Usuário RRS feed

  • Pergunta

  •   Já futuquei por aí e não achei ou entendi o que achei...
      Preciso de um script que me retorne as estatísticas de uso de um determinado login que eu passarei por parâmetro.

    » Total de acessos
    » Data do primeiro acesso
    » Data do último acesso
    » Scripts executados pelo usuário (se possível)
    » Tempo de run de cada um dos scripts (se possível)

    Alguém tem alguma ideia de como fazer isso ?

    terça-feira, 29 de setembro de 2015 19:40

Respostas

  • SupremeFoxPC,

    Poxa vida, acredito que alguns dados não vamos conseguir obter, o script abaixo pode lhe ajudar a obter algumas informações:

    -- Exibindo informações sobre logins utilizados em Autenticação SQL --
    
    CREATE TABLE #LoginInfo
    (BadPasswordCount SQL_VARIANT
    ,BadPasswordTime SQL_VARIANT
    ,DaysUntilExpiration SQL_VARIANT
    ,DefaultDatabase SQL_VARIANT
    ,DefaultLanguage SQL_VARIANT
    ,HistoryLength SQL_VARIANT
    ,IsExpired SQL_VARIANT
    ,IsLocked SQL_VARIANT
    ,IsMustChange SQL_VARIANT
    ,LockoutTime SQL_VARIANT
    ,PasswordHash SQL_VARIANT
    ,PasswordLastSetTime SQL_VARIANT
    ,username VARCHAR(25))
    
    DECLARE @BadPasswordCount SQL_VARIANT
    ,@BadPasswordTime SQL_VARIANT
    ,@DaysUntilExpiration SQL_VARIANT
    ,@DefaultDatabase SQL_VARIANT
    ,@DefaultLanguage SQL_VARIANT
    ,@HistoryLength SQL_VARIANT
    ,@IsExpired SQL_VARIANT
    ,@IsLocked SQL_VARIANT
    ,@IsMustChange SQL_VARIANT
    ,@LockoutTime SQL_VARIANT
    ,@PasswordHash SQL_VARIANT
    ,@PasswordLastSetTime SQL_VARIANT
    ,@username VARCHAR(25)
    
    DECLARE useracct_cursor CURSOR FOR
    SELECT NAME FROM sys.server_principals WHERE (type = 'S' AND NAME NOT LIKE '##%')
    
    OPEN useracct_cursor
    FETCH NEXT FROM useracct_cursor INTO @username
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    SET @BadPasswordCount = (SELECT loginproperty(@username, 'BadPasswordCount'))
    SET @BadPasswordTime = (SELECT loginproperty(@username, 'BadPasswordTime'))
    SET @DaysUntilExpiration = (SELECT loginproperty(@username, 'DaysUntilExpiration'))
    SET @DefaultDatabase = (SELECT loginproperty(@username, 'DefaultDatabase'))
    SET @DefaultLanguage = (SELECT loginproperty(@username, 'DefaultLanguage'))
    SET @HistoryLength = (SELECT loginproperty(@username, 'HistoryLength'))
    SET @IsExpired = (SELECT loginproperty(@username, 'IsExpired'))
    SET @IsLocked = (SELECT loginproperty(@username, 'IsLocked'))
    SET @IsMustChange = (SELECT loginproperty(@username, 'IsMustChange'))
    SET @LockoutTime = (SELECT loginproperty(@username, 'LockoutTime'))
    SET @PasswordHash = (SELECT loginproperty(@username, 'PasswordHash'))
    SET @PasswordLastSetTime = (SELECT loginproperty(@username, 'PasswordLastSetTime'))
    
    INSERT INTO #LoginInfo
     ( BadPasswordCount ,
     BadPasswordTime ,
     DaysUntilExpiration ,
     DefaultDatabase ,
     DefaultLanguage ,
     HistoryLength ,
     IsExpired ,
     IsLocked ,
     IsMustChange ,
     LockoutTime ,
     PasswordHash ,
     PasswordLastSetTime ,
     username
     )
    VALUES ( @BadPasswordCount 
    ,@BadPasswordTime 
    ,@DaysUntilExpiration 
    ,@DefaultDatabase
    ,@DefaultLanguage
    ,@HistoryLength 
    ,@IsExpired 
    ,@IsLocked 
    ,@IsMustChange 
    ,@LockoutTime 
    ,@PasswordHash 
    ,@PasswordLastSetTime 
    ,@username 
     )
    
    FETCH NEXT FROM useracct_cursor INTO @username
    END
    
    CLOSE useracct_cursor
    DEALLOCATE useracct_cursor
    
    SELECT username AS 'SQL Login Name'
    ,BadPasswordCount AS 'Bad Password Count'
    ,BadPasswordTime AS 'Bad Password Time'
    ,DaysUntilExpiration AS 'Days Until Expiration'
    ,DefaultDatabase AS 'Default Database'
    ,DefaultLanguage AS 'Default Language'
    ,HistoryLength AS 'History Length'
    ,IsExpired AS 'Is Expired'
    ,IsLocked AS 'Is Locked'
    ,IsMustChange AS 'Is Must Change'
    ,LockoutTime AS 'Lockout Time'
    ,PasswordHash AS 'Password Hash'
    ,PasswordLastSetTime AS 'Password Last Set Time'
    FROM #LoginInfo
    
    DROP TABLE #LoginInfo
    GO
    

    Este outro script que utiliza as sys.server_principals e sys.server_permissions, pode te apresentar algumas outras informações:

    SELECT pr.principal_id, pr.name, pr.type_desc, 
          pe.state_desc, pe.permission_name , pr.create_date, pr.modify_date
      FROM sys.server_principals AS pr 
      JOIN sys.server_permissions AS pe 
          ON pe.grantee_principal_id = pr.principal_id;
    

    Acredito que os dados de scripts executados e tempo de execução dos scripts inicialmente não consigo imaginar como obter, pois isso vai depender da forma que você esta gerando estes scripts.

    Talvez uma opção seja através do SSDT(SQL Server Data Tools) substituto do SSIS que apresenta uma funcionalidade nova chamada SSISDB que apresenta uma série de informações sobre uma instância ou servidor SQL Server.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta SupremeFoxPC segunda-feira, 12 de outubro de 2015 09:33
    quarta-feira, 30 de setembro de 2015 14:33

Todas as Respostas

  • SupremeFoxPC,

    Poxa vida, acredito que alguns dados não vamos conseguir obter, o script abaixo pode lhe ajudar a obter algumas informações:

    -- Exibindo informações sobre logins utilizados em Autenticação SQL --
    
    CREATE TABLE #LoginInfo
    (BadPasswordCount SQL_VARIANT
    ,BadPasswordTime SQL_VARIANT
    ,DaysUntilExpiration SQL_VARIANT
    ,DefaultDatabase SQL_VARIANT
    ,DefaultLanguage SQL_VARIANT
    ,HistoryLength SQL_VARIANT
    ,IsExpired SQL_VARIANT
    ,IsLocked SQL_VARIANT
    ,IsMustChange SQL_VARIANT
    ,LockoutTime SQL_VARIANT
    ,PasswordHash SQL_VARIANT
    ,PasswordLastSetTime SQL_VARIANT
    ,username VARCHAR(25))
    
    DECLARE @BadPasswordCount SQL_VARIANT
    ,@BadPasswordTime SQL_VARIANT
    ,@DaysUntilExpiration SQL_VARIANT
    ,@DefaultDatabase SQL_VARIANT
    ,@DefaultLanguage SQL_VARIANT
    ,@HistoryLength SQL_VARIANT
    ,@IsExpired SQL_VARIANT
    ,@IsLocked SQL_VARIANT
    ,@IsMustChange SQL_VARIANT
    ,@LockoutTime SQL_VARIANT
    ,@PasswordHash SQL_VARIANT
    ,@PasswordLastSetTime SQL_VARIANT
    ,@username VARCHAR(25)
    
    DECLARE useracct_cursor CURSOR FOR
    SELECT NAME FROM sys.server_principals WHERE (type = 'S' AND NAME NOT LIKE '##%')
    
    OPEN useracct_cursor
    FETCH NEXT FROM useracct_cursor INTO @username
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    SET @BadPasswordCount = (SELECT loginproperty(@username, 'BadPasswordCount'))
    SET @BadPasswordTime = (SELECT loginproperty(@username, 'BadPasswordTime'))
    SET @DaysUntilExpiration = (SELECT loginproperty(@username, 'DaysUntilExpiration'))
    SET @DefaultDatabase = (SELECT loginproperty(@username, 'DefaultDatabase'))
    SET @DefaultLanguage = (SELECT loginproperty(@username, 'DefaultLanguage'))
    SET @HistoryLength = (SELECT loginproperty(@username, 'HistoryLength'))
    SET @IsExpired = (SELECT loginproperty(@username, 'IsExpired'))
    SET @IsLocked = (SELECT loginproperty(@username, 'IsLocked'))
    SET @IsMustChange = (SELECT loginproperty(@username, 'IsMustChange'))
    SET @LockoutTime = (SELECT loginproperty(@username, 'LockoutTime'))
    SET @PasswordHash = (SELECT loginproperty(@username, 'PasswordHash'))
    SET @PasswordLastSetTime = (SELECT loginproperty(@username, 'PasswordLastSetTime'))
    
    INSERT INTO #LoginInfo
     ( BadPasswordCount ,
     BadPasswordTime ,
     DaysUntilExpiration ,
     DefaultDatabase ,
     DefaultLanguage ,
     HistoryLength ,
     IsExpired ,
     IsLocked ,
     IsMustChange ,
     LockoutTime ,
     PasswordHash ,
     PasswordLastSetTime ,
     username
     )
    VALUES ( @BadPasswordCount 
    ,@BadPasswordTime 
    ,@DaysUntilExpiration 
    ,@DefaultDatabase
    ,@DefaultLanguage
    ,@HistoryLength 
    ,@IsExpired 
    ,@IsLocked 
    ,@IsMustChange 
    ,@LockoutTime 
    ,@PasswordHash 
    ,@PasswordLastSetTime 
    ,@username 
     )
    
    FETCH NEXT FROM useracct_cursor INTO @username
    END
    
    CLOSE useracct_cursor
    DEALLOCATE useracct_cursor
    
    SELECT username AS 'SQL Login Name'
    ,BadPasswordCount AS 'Bad Password Count'
    ,BadPasswordTime AS 'Bad Password Time'
    ,DaysUntilExpiration AS 'Days Until Expiration'
    ,DefaultDatabase AS 'Default Database'
    ,DefaultLanguage AS 'Default Language'
    ,HistoryLength AS 'History Length'
    ,IsExpired AS 'Is Expired'
    ,IsLocked AS 'Is Locked'
    ,IsMustChange AS 'Is Must Change'
    ,LockoutTime AS 'Lockout Time'
    ,PasswordHash AS 'Password Hash'
    ,PasswordLastSetTime AS 'Password Last Set Time'
    FROM #LoginInfo
    
    DROP TABLE #LoginInfo
    GO
    

    Este outro script que utiliza as sys.server_principals e sys.server_permissions, pode te apresentar algumas outras informações:

    SELECT pr.principal_id, pr.name, pr.type_desc, 
          pe.state_desc, pe.permission_name , pr.create_date, pr.modify_date
      FROM sys.server_principals AS pr 
      JOIN sys.server_permissions AS pe 
          ON pe.grantee_principal_id = pr.principal_id;
    

    Acredito que os dados de scripts executados e tempo de execução dos scripts inicialmente não consigo imaginar como obter, pois isso vai depender da forma que você esta gerando estes scripts.

    Talvez uma opção seja através do SSDT(SQL Server Data Tools) substituto do SSIS que apresenta uma funcionalidade nova chamada SSISDB que apresenta uma série de informações sobre uma instância ou servidor SQL Server.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta SupremeFoxPC segunda-feira, 12 de outubro de 2015 09:33
    quarta-feira, 30 de setembro de 2015 14:33
  • Camarada... Isso já ajudou muuuuuito!
    Valeu mesmo!!!!
    segunda-feira, 12 de outubro de 2015 09:34