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?
1 2 3 4 5 6 7 |
--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?
1 2 3 4 5 6 7 8 9 10 11 12 |
--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?
1 2 3 4 5 6 7 8 9 10 |
--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?
1 2 3 4 5 6 7 8 9 |
--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.
Pingback: sql server check login permissions Online Portal Client Support Activation Password Information - banklogining.com
Pingback: sql login details Online Portal Client Support Activation Password Information - banklogining.com