1

Using SQL Server 2008R2.

We are trying to audit user logins to see both how long user accounts have been inactive and to see how many licenses we will need coming up. I am currently pulling information from the following job:

Insert into [User_Login_Audit]
Select 
    login_name,
    max (login_time) as last_login_time,
    last_successful_logon, 
    (select @@servername) as server_instance,
    getdate()
from sys.dm_exec_sessions 
group by login_name, last_successful_logon;

I am then using linked servers to pull the information in via the following query:

Use B_DBA
GO


SELECT 
    [Login_name]
    ,max([last_login_time]) as Last_login_date
    ,[server_instance],
    DATEDIFF(day, getdate(), max([last_login_time])) Days
FROM [B_DBA].[dbo].[User_Login_Audit]
where  Login_name not in ('NT AUTHORITY\SYSTEM', 'sa','')
    and last_successful_logon is not null
group by Login_name,server_instance

This is pulling the results of

Server Name | Server\instance Login_name | PROD\account_name Last_login_date | 2016-10-16 11:29:05.950 server_instance | Server\instance Days |-24

The problem I have with this is that apparently sys.dm_exec_sessions is not giving me current active users that are in Security now, but all users that have logged in, many of which have been removed from current access in security.

What I am needing is a way to pull Sql Users, Windows Users and Windows User Group logins that are currently in Database security. I need to know the last time the user logged in so I can calculate the number of days ie. 30,60,90.

What I am doing is giving me information, but not the most accurate information and is requiring a great deal of extra work. I would appreciate different options on this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bwilliamson
  • 391
  • 1
  • 13
  • 1
    `sys.dm_exec_sessions` contains only active sessions (as in, there's a user connected right now). From SQL Server 2012 onwards, each row also has a `database_id` column that details the user's current database. Prior to SQL Server 2012, this info is, as far as I know, not available. The closest you can get to this is cross-correlating with `sys.dm_exec_connections`, using `most_recent_sql_handle` and then `sys.dm_exec_sql_text` to get at the `dbid` of their last query. While suggestive, this is still not necessarily the database they're currently using. – Jeroen Mostert Nov 09 '16 at 18:55
  • Basically I just need to know when they last accessed any database on the instance. Is that data stored in any repository that I can get at? – bwilliamson Nov 09 '16 at 18:58
  • So something like https://msdn.microsoft.com/en-us/library/ms175850.aspx ? – S3S Nov 09 '16 at 19:07
  • You can't get information about users who aren't connected. For that you'll need proper login auditing, as @scsimon points out (or you could use a custom profiler trace or extended event session, but there's no ready-to-go data in a table or view somewhere). – Jeroen Mostert Nov 09 '16 at 19:14
  • I think for what you are attempting to get this info found here will be of use to you. http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database – Neo Nov 09 '16 at 19:44
  • This I believe will give you the last time the user logged in by the user name "select name, accdate from sys.syslogins" – Neo Nov 09 '16 at 19:46
  • @MisterPositive The first link showed every permission which I don't really need. The second is giving me very strange results as far as accdate and doesn't look accurate. I'll investigate why I am getting the dates it is showing. – bwilliamson Nov 09 '16 at 19:51
  • @bwilliamson take a look at the sys.syslogins definition. That should be useful to you, if not, best of luck. :-) – Neo Nov 09 '16 at 19:51

0 Answers0