SQL Scripts: How To Check Logins And Permissions

Security and permissions are a big part of a DBA’s job, and being able to find out things such as who has elevated login permissions, or when the last time a login was used is important.

People sometimes get SQL Server “logins” and “users” confused, or think they are the same thing.   A “login” allows (or denies) access to a SQL Server instance.  A “user” allows (or denies) access to a specific database on that instance.   Usually, a user is tied to a login, although you can have a user that is not tied to a login (known as a loginless user).

Here are a few scripts, focused around login usage, that I use to help me answer each of the listed questions.  Since we are checking logins here, we are only looking at server access, not user permissions on individual databases.  Most of these queries are based off of sys.dm_exec_sessions, which is a DMV that shows information about all active user connections.

When was the last time a login was used?

 

Which logins have logged in within the last X hours?

 

How many times has each login logged in within the last X hours?

 

Which logins have Sysadmin access?

 

I should also mention that the results of these queries will only go back to the last SQL Server start time, anything that occurred before then will not be available.

This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.

 

(Visited 29,589 times, 1 visits today)