SQL Last login and Role

Vijay Kumar 2,016 Reputation points
2022-02-07T22:39:52.513+00:00

Hi Team,

Looking for T-SQL to fetch the details of users who have sysadmin role and last login: (both SQL users and windows users)

Username DatabaseName Role Lastloggedin

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-02-08T08:47:13.777+00:00

    Just one more thing: Note that the query that I gave you will not really give users who are in sysadmin. It will give server principals. Specifically, that can be a Windows AD group, and if you want the users in that group, you need to query the AD. (Which can be done from SQL Server, but I don't know how to do that myself, as I never had had the need.)

    And here you also have why SQL Server does not track last login by default: many of the users who log into SQL Server are not registered there directly, only through an AD group. (Of course, this depends on how it works at your site. But I believe it is very common to arrange things through the AD. For instance, I'm looking at a production server and there are around 50 rows in sys.server_principals. Yet there are a couple of thousand users who access this system.)

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-02-07T23:09:32.2+00:00

    To get members of sysadmin: 

    SELECT name FROM sys.server_principals WHERE is_srvrolemember('sysadmin', name) = 1

    Not sure what you mean with DabaseName - members of sysadmin have access to all databases.

    If you want a query to get last login, you will need to tell us how you audit logins. Which you need to do to be able to retrieve that information. SQL Server does not track it by default.

  2. LiHong-MSFT 10,046 Reputation points
    2022-02-08T06:09:47.813+00:00

    Hi @Vijay Kumar
    The use of sys.dm_exec_sessions only shows information from active sessions of the instance.
    If the instance is restarted or the session ends running, that information will already be lost. If when your last login data collection routine is performed and you have no more active sessions for that user, you will also not have this information available.
    You can use Login Auditing or Use a logon trigger.
    For more details,please refer to this article: SQL Server - How to know the date of a user's last login

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments