Useful Scripts For SQL Server Logins and Permissions

Since security and permissions are a big part of a DBA’s job, it’s important to be able to find out things like who has elevated login permissions or when a login was last used. Here are a few queries to help you check your server and database access. Most of these scripts are based off of sys.dm_exec_sessions, a DMV that shows information about all active user connections. 

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 won’t be available.

Logins Vs. Users

First, let’s take a look at the difference between “logins” and “users” in SQL Server, as people sometimes get them confused, or think they’re the same thing. A “login” allows access to a SQL Server instance. A “user” allows 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).

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];

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

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

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]

Checking A User’s Access To Databases

This query will return a list of databases that the specified user has access to. This will work for SQL Server Logins as well as Active Directory Logins. This is done by specifying EXECUTE AS LOGIN just before the query. (also be sure to specify REVERT after the query runs)

It should be noted that this query only shows you what databases the user can access, not the permissions the user has on the databases.

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This
	SELECT [name]
	FROM MASTER.sys.databases
	WHERE HAS_DBACCESS([name]) = 1
REVERT

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.