Last Login date checking

Question

Post reply

Last Login date checking

SQL Galaxy

One Orange Chip

Points: 29472

April 15, 2022 at 11:52 am

#4016685

Hi,

copied from - https://en.dirceuresende.com/blog/sql-server-como-saber-a-data-do-ultimo-login-de-um-usuario/

executing fine with no errors.

I included one new Colum name default_database_name varchar(100), but execution failed.

Msg 209, Level 16, State 1, Line 77

Ambiguous column name 'default_database_name'.

Pl. help how to fix this error. Thanks

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

IF (OBJECT_ID('tempdb..#Arquivos_Log') IS NOT NULL) DROP TABLE #Arquivos_Log
CREATE TABLE #Arquivos_Log ( 
    [idLog] INT, 
    [dtLog] NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AI, 
    [tamanhoLog] INT 
)

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
CREATE TABLE #Dados (
    [LogDate] DATETIME, 
    [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, 
    [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI,
    [User] AS (SUBSTRING(REPLACE([Text], 'Login succeeded for user ''', ''), 1, CHARINDEX('''', REPLACE([Text], 'Login succeeded for user ''', '')) - 1))
)


INSERT INTO #Arquivos_Log
EXEC sys.sp_enumerrorlogs


DECLARE
    @Contador INT = 0,
    @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log)
    
 
WHILE(@Contador < @Total)
BEGIN
    
    INSERT INTO #Dados (LogDate, ProcessInfo, [Text]) 
    EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login succeeded for user', NULL, NULL, NULL
 
    SET @Contador += 1
    
END



IF (OBJECT_ID('tempdb..#UltimoLogin') IS NOT NULL) DROP TABLE #UltimoLogin
CREATE TABLE #UltimoLogin (
    [User] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    LogDate DATETIME NOT NULL
)

INSERT INTO #UltimoLogin
SELECT 
    [User], 
    MAX(LogDate) AS LogDate
FROM
    #Dados
GROUP BY
    [User]



-- create table
IF (OBJECT_ID('dbo.LastLogin') IS NULL)
BEGIN
    
    -- DROP TABLE dbo.LastLogin
    CREATE TABLE dbo.LastLogin (
default_database_name varchar(100),
        Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        CreateDate DATETIME,
        LastLogin DATETIME NULL,
        DaysSinceLastLogin AS (DATEDIFF(DAY, ISNULL(LastLogin, CreateDate), CONVERT(DATE, GETDATE())))
    )

END

-- Insere os logins criados na instância
INSERT INTO dbo.LastLogin (default_database_name,Username, CreateDate)
SELECT
[default_database_name],
    [name],
    create_date
FROM
    sys.server_principals A
    LEFT JOIN dbo.LastLogin B ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = B.Username
WHERE
    is_fixed_role = 0
    AND [name] NOT LIKE 'NT %'
    AND [name] NOT LIKE '##%'
    AND B.Username IS NULL
    AND A.[type] IN ('S', 'U')


-- Atualiza a tabela de histórico com os dados atuais
UPDATE A
SET
    A.LastLogin = B.LogDate
FROM
    dbo.LastLogin A
    JOIN #UltimoLogin B ON A.Username = B.[User]
WHERE
    ISNULL(A.LastLogin, '1900-01-01') <> B.LogDate


--SELECT * 
--FROM dbo.LastLogin

SET @xml = CAST(( SELECT [default_database_name] AS 'td','', [Username] AS 'td','',[createdate] AS 'td','',
 [Lastlogin] AS 'td','', [DaysSinceLastLogin] AS 'td',''
FROM dbo.LastLogin 
--where type not in ('R', 'C') --and create_date >=GETDATE() -60
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body>SQL Server database users reviewed . 
<table border = 1> 
<tr>
<th> default_database_name </th> <th> Username </th> <th> createdate </th> <th> Lastlogin </th><th>DaysSinceLastLogin </th></tr>'    
 
SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBmail', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'emailid@xxx.com', -- replace with your email address
@copy_recipients = 'emailid@xxx.com, -- replace with your email address
@subject = ‘SQL Server database users reviewed ' ;

May 13, 2022 at 10:20 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27045 More actions · Answer 1

The table #LastLogin as well as sys.server_principals contain the column default_database_name.

In the query where you join both of these, you are referencing that column. SQL cannot determine which table it needs to use. Preface the column name with the alias for the table.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/