Export list of active users from SQL

Hi,

I've been asked to create a list of all user to see if there a being used. I can see in this forum that meny have asked for it as it's not made out of the box in the system. So I created a SQL script to find the users and give an nice overview of the user. But when looking at the "LastLoginTime" and "LastLogoutTime" it give me a weird number like "1563444162" can anyone help me how to convert this number?

  --Create a table to hold the query results
CREATE TABLE #mftusers
    (dbid smallint, dbname sysname, system_license varchar(99));

DECLARE @fixedname nvarchar(150);

--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
    SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
    FROM master.sys.databases
    WHERE state_desc = 'ONLINE'
	AND name like 'ServUmft_%'
    AND HAS_DBACCESS(name) = 1 
    AND name NOT IN ('master','msdb','model','tempdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;

WHILE @@FETCH_STATUS = 0   
BEGIN   
       EXEC ('USE ' + @fixedname + ';' + 
      'SELECT
      [LastLoginTime]
      ,[LastLogoutTime]
      ,[Logins]
      ,[Logouts]
      ,[MostConcurrentLogins]
      ,[LoginID]
      ,[Organization]
      ,[FullName]
      ,[EmailAddress]
      ,[HomeDir]
  FROM
       [dbo].[SUUsers];');

       FETCH NEXT FROM dbCursor INTO @fixedname;
END;   

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #mftusers

DROP TABLE #mftusers

Parents
  • I managed to find a solution to the timestamp, it is a Unix timestamp known as EPOCH, it is the time in seconds since 1970-01-01. I have converted it to date.

      --Create a table to hold the query results
    CREATE TABLE #mftusers
        (dbid smallint, dbname sysname, system_license varchar(99));
    
    DECLARE @fixedname nvarchar(150);
    
    --Include or exclude whatever databases you want
    DECLARE dbCursor CURSOR FOR
        SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
        FROM master.sys.databases
        WHERE state_desc = 'ONLINE'
    	AND name like 'ServUmft_%'
        AND HAS_DBACCESS(name) = 1 
        AND name NOT IN ('master','msdb','model','tempdb');
    
    OPEN dbCursor;
    FETCH NEXT FROM dbCursor INTO @fixedname;
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           EXEC ('USE ' + @fixedname + ';' + 
          'SELECT 
           dateadd(dd,LastLoginTime/60/60/24, ''19700101'') as Lastlogin
          ,dateadd(dd,LastLogOutTime/60/60/24, ''19700101'') as Lastlogout
          ,[Logins]
          ,[Logouts]
          ,[MostConcurrentLogins]
          ,[LoginID]
          ,[Organization]
          ,[FullName]
          ,[EmailAddress]
          ,[HomeDir]
      FROM
           [dbo].[SUUsers];');
    
           FETCH NEXT FROM dbCursor INTO @fixedname;
    END;   
    
    CLOSE dbCursor;
    DEALLOCATE dbCursor;
    
    SELECT * FROM #mftusers
    
    DROP TABLE #mftusers

Reply
  • I managed to find a solution to the timestamp, it is a Unix timestamp known as EPOCH, it is the time in seconds since 1970-01-01. I have converted it to date.

      --Create a table to hold the query results
    CREATE TABLE #mftusers
        (dbid smallint, dbname sysname, system_license varchar(99));
    
    DECLARE @fixedname nvarchar(150);
    
    --Include or exclude whatever databases you want
    DECLARE dbCursor CURSOR FOR
        SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
        FROM master.sys.databases
        WHERE state_desc = 'ONLINE'
    	AND name like 'ServUmft_%'
        AND HAS_DBACCESS(name) = 1 
        AND name NOT IN ('master','msdb','model','tempdb');
    
    OPEN dbCursor;
    FETCH NEXT FROM dbCursor INTO @fixedname;
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           EXEC ('USE ' + @fixedname + ';' + 
          'SELECT 
           dateadd(dd,LastLoginTime/60/60/24, ''19700101'') as Lastlogin
          ,dateadd(dd,LastLogOutTime/60/60/24, ''19700101'') as Lastlogout
          ,[Logins]
          ,[Logouts]
          ,[MostConcurrentLogins]
          ,[LoginID]
          ,[Organization]
          ,[FullName]
          ,[EmailAddress]
          ,[HomeDir]
      FROM
           [dbo].[SUUsers];');
    
           FETCH NEXT FROM dbCursor INTO @fixedname;
    END;   
    
    CLOSE dbCursor;
    DEALLOCATE dbCursor;
    
    SELECT * FROM #mftusers
    
    DROP TABLE #mftusers

Children
No Data