The other day my wife and I were driving around and noticed that a Little Caesar's location had shown up near our house. The kids love their pizza and at the price, you can't beat it. So we decided after karate tonight that we'd pick up pizzas from there and head home to consume the delectable product. So we do just that, only after pulling up to the location we're confronted with the sign, "Coming Soon." Argh. No Little Caesar's pizza. We end up going another place, but talk about dashed expectations.

I feel the same way whenever I look at sys.sql_logins and realize that there's not a last logon column. I understand how difficult this can be (and how useless the information may be) when it comes to tracking this information for Windows-based logins. After all, if we grant access to SQL Server via a group, then the only login entry we'll see is for the group itself. So what is the best way to track the login information? Whenever any Windows user that is a member of the group logs in? If so, does this really do us any good? Probably not.

But when it comes to SQL Server-based logins, I frequently see questions about how to determine when a login last connected to SQL Server and how best to determine that information. The answer we always have to give is that the information isn't available within SQL Server. You might be able to track it in the SQL Server error log or the operating system's application event log, but you'll need another system to keep track of that information, especially as events roll information out of those two sources. So we end up talking about building workarounds like logon triggers that fire and store the information and we have to implement those solutions on every SQL Server we build. I would prefer that it was built-in and that we didnt have to build a custom solution every time.

To put things in perspective, we do track an account's last logon in Active Directory. This information is stored locally on each domain controller, meaning to have truly accurate information, you have to query every domain controller. As of Windows Server 2003 Active Directory, this information is replicated on a regular basis, but not quick enough to be accurate for audit purposes. I can understand the rationale. There's a lot going on within Active Directory (AD) and a lot of information that needs to be distributed, especially in a multiple master model which reflects Active Directory's domain controller architecture. And when you consider that there are potentially hundreds of domain controllers, you try to make sure what gets replicated is what absolutely must be replicated (like security group changes, account deletions, etc.).

A SQL Server login doesn't suffer from the same issues with respect to the multiple master model. The login is only valid for the specific server. I don't know if there are the issue is a potential locking issue or something of that sort which prevents the storing of such information. However, AD would have the same issue and handles it. Within SQL Server, if auditing is turned on, there is already some work having to be done on this front, except the information is being written to a couple of files. So maybe I'm not seeing something that I should, but I would think that adding last logon information is doable. I know from an audit perspective it would be helpful. From an administration and security perspective it would be, too, because it would indicate to me whether or not I can delete a login based on inactivity. And along those same lines, if I have an application that has a SQL Server-based login, folks have made changes so it's either using another login (such as a Windows login) and I can't really afford to have that app have any functionality unavailable, then having that last logon information will tell me if all the changes were a success without an outage for my users. So that's another reason I'd like to see it implemented in a future version of SQL Server.