Dica – Exibindo informações sobre Logins utilizados em Autenticação SQL para SQL Server 2005, 2008 e R2.

Pessoal, bom dia!

Tudo bem?  Graças a deus estou bem, trabalhando e estudando muito.

Bom, na dica de hoje vou destacar como podemos obter informações de um dos processos mais importantes do SQL Server, estou me referindo ao Processo de Autenticação de Usuários.

A autenticação de todo e qualquer usuário em uma aplicação é a técnica de identificação deste indivíduo, que indica para os mecanismos de segurança que esta tentando estabelecer o acesso ou conexão com uma Base de Dados ou qualquer outro tipo de objeto responsável em prover informações.

No SQL Server isso não é diferente, como todos devem saber existem dois tipos de Autenticação: 

• Windows Authentication – Utilizando contas de usuários existentes na máquina local ou em seu Controlador de Domínio;

 

• SQL Server Authentication ou Mixed Mode – Conhecido como Modo Misto que possibilita utilizar ao mesmo tempo contas de usuários Windows ou contas de usuário SQL Server.

 

Neste momento não vou me preocupar em querer definir qual dos métodos é mais seguro, este não é objetivo desta dica.

Muito bem, no código de exemplo utilizado nesta dica, você terá a possibilidade de obter uma relação de informações sobre as autenticações de usuários realizadas no SQL Server através do Mixed Mode.

A seguir apresento o Código 1:

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’,

BadPasswordTimeAS’Bad Password Time’,

DaysUntilExpiration AS ‘Days Until Expiration’,

DefaultDatabaseAS’Default Database’,

DefaultLanguageAS’Default Language’,

HistoryLengthAS’History Length’,

IsExpiredAS’Is Expired’,

IsLockedAS’Is Locked’,

IsMustChangeAS’Is Must Change’,

LockoutTimeAS’Lockout Time’,

PasswordHashAS’Password Hash’,

PasswordLastSetTimeAS’Password Last Set Time’

FROM #LoginInfo

 

DROP TABLE #LoginInfo

GO

Os dois elementos mais importantes para o funcionamento de código estão relacionados a System View:

• sys.server_principals: Existente a partir do SQL Server 2005, tem a finalidade de retornar uma linha para cada Server Principal existente em uma instância SQL Server.

 

• LoginProperty(): Existente a partir do SQL Server 2005, tem a finalidade de retornar informações relacionadas aos processos de Logon de usuários, com base, no seu conjunto de opções apresentados abaixo na Tabela 1.

Valor	Descrição
BadPasswordCount	Retorna o número de tentativas consecutivas de fazer logon com uma senha incorreta.
BadPasswordTime	Retorna a hora da última tentativa de fazer logon com uma senha incorreta.
DaysUntilExpiration	Retorna o número de dias para que a senha expire.
DefaultDatabase	Retorna o banco de dados padrão do logon do SQL Server como armazenado em metadados ou o mestre se nenhum banco de dados for especificado. Retorna NULL para usuários provisionados não-SQL Server; por exemplo usuários autenticados do Windows.
DefaultLanguage	Retorna a linguagem padrão de logon como armazenado em metadados. Retorna NULL para usuários provisionados não-SQL Server, por exemplo usuários autenticados do Windows.
HistoryLength	Retorna o comprimento de hora que o logon que tem controlado usando o mecanismo de imposição de diretiva de senha.
IsExpired	Retorna informações que indicarão se o logon está expirou.
IsLocked	Retorna informações que indicarão se o logon está bloqueado.
IsMustChange	Retorna informações que indicarão se o logon deve alterar sua senha na próxima vez conectar.
LockoutTime	Retorna a data em que o logon no SQL Server foi bloqueado porque excedeu o número permitido de tentativas de logon com falha.
PasswordHash	Retorna o hash da senha.
PasswordLastSetTime	Retorna a data em que a senha atual foi definida.

 

Tabela 1 – Relação de opções existentes para a função LoginProperty().

 

Bem pessoal, vou encerrando esta dica, espero que todos possam ter gostado e que este post possa ajudar.

Mais uma vez obrigado. 

Até mais.