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?


--list of logins and last time each logged in
SELECT [Login] = login_name 
	,[Last Login Time] = MAX(login_time)
FROM sys.dm_exec_sessions
GROUP BY [login_name];

--list of logins and last time each logged in

SELECT [Login] = login_name

,[Last Login Time] = MAX(login_time)

FROM sys.dm_exec_sessions

GROUP BY [login_name];

Which logins have logged in within the last X hours?


--all logins in the last 4 hours
SELECT [Login] = login_name
	,[Last Login Time] = login_time
	,[Host] = HOST_NAME
	,[Program] = PROGRAM_NAME
	,[Client Interface] =  client_interface_name
	,[Database] = DB_NAME(database_id)
FROM sys.dm_exec_sessions
WHERE [login_time] > DATEADD(HH,-4,getdate())--modify date as needed
ORDER BY [login_time] desc

--all logins in the last 4 hours

SELECT [Login] = login_name

,[Last Login Time] = login_time

,[Host] = HOST_NAME

,[Program] = PROGRAM_NAME

,[Client Interface] = client_interface_name

,[Database] = DB_NAME(database_id)

FROM sys.dm_exec_sessions

WHERE [login_time] > DATEADD(HH,-4,getdate())--modify date as needed

ORDER BY [login_time] desc

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


--login counts for the last 4 hours
SELECT [Login] = login_name
	,[Last Login Time] = MAX(login_time)
	,[Number Of Logins] = COUNT(*)
FROM sys.dm_exec_sessions
WHERE [login_time] > DATEADD(HH,-4,getdate())--modify date as needed
GROUP BY [login_name]
ORDER BY [Login] desc

--login counts for the last 4 hours

SELECT [Login] = login_name

,[Last Login Time] = MAX(login_time)

,[Number Of Logins] = COUNT(*)

FROM sys.dm_exec_sessions

WHERE [login_time] > DATEADD(HH,-4,getdate())--modify date as needed

GROUP BY [login_name]

ORDER BY [Login] desc

Which logins have Sysadmin access?


--check for logins with sysadmin access
SELECT [Login] = name
	,[Login Type] = type_desc
	,[Disabled] = is_disabled
FROM     master.sys.server_principals 
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY [Login]

--check for logins with sysadmin access

SELECT [Login] = name

,[Login Type] = type_desc

,[Disabled] = is_disabled

FROM master.sys.server_principals

WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1

ORDER BY [Login]

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,904 times, 1 visits today)

SQL Scripts: How To Check Logins And Permissions

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?

Related

2 thoughts on “SQL Scripts: How To Check Logins And Permissions”

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?

Share this:

Related

2 thoughts on “SQL Scripts: How To Check Logins And Permissions”