The Logins module collects login data – for both SQL Server logins and Windows logins with access to SQL Server – for every managed instance.  For the most recent collection of logins, use the view Collector.LoginsCurrent.

Note that data from the Logins module also supports the Clone Users Module, the Login Password Strength Utility, and the SID Server Utility, among others.
 

Tables

Collector.Logins

Stores the collections of login data.

Definitions for many of the Collector.Logins columns can be found in the MSDN article for the sys.syslogins*: https://msdn.microsoft.com/en-us/library/ms178593.aspx
 
Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
sid varbinary See the sys.syslogins MSDN article.
status smallint See the sys.syslogins MSDN article.
createdate datetime See the sys.syslogins MSDN article.
updatedate datetime See the sys.syslogins MSDN article.
accdate datetime See the sys.syslogins MSDN article.
totcpu int See the sys.syslogins MSDN article.
totio int See the sys.syslogins MSDN article.
spacelimit int See the sys.syslogins MSDN article.
timelimit int See the sys.syslogins MSDN article.
resultlimit int See the sys.syslogins MSDN article.
name sysname See the sys.syslogins MSDN article.
dbname sysname See the sys.syslogins MSDN article.
password varbinary The hashed password.
language sysname See the sys.syslogins MSDN article.
denylogin int See the sys.syslogins MSDN article.
hasaccess int See the sys.syslogins MSDN article.
isntname int See the sys.syslogins MSDN article.
isntgroup int See the sys.syslogins MSDN article.
isntuser int See the sys.syslogins MSDN article.
sysadmin int See the sys.syslogins MSDN article.
securityadmin int See the sys.syslogins MSDN article.
serveradmin int See the sys.syslogins MSDN article.
setupadmin int See the sys.syslogins MSDN article.
processadmin int See the sys.syslogins MSDN article.
diskadmin int See the sys.syslogins MSDN article.
dbcreator int See the sys.syslogins MSDN article.
bulkadmin int See the sys.syslogins MSDN article.
loginname sysname See the sys.syslogins MSDN article.
BadPasswordCount int Number of consecutive attempts to log in with an incorrect password.
BadPasswordTime datetime The time of the last attempt to log in with an incorrect password.
HistoryLength datetime The number of passwords tracked for the login (using password policy enforcement). This will be 0 if the password policy is not enforced.
PasswordLastSetTime datetime The date when the current password was set.
PasswordHash varchar The algorithm used to hash the password.
LoginType varchar The login type. (E.g., SqlLogin, WindowsUser, etc.)
DateLastModified datetime The date and time when the principal definition was last modified.
IsDisabled bit Indicates whether the login is disabled or not.
IsLocked bit Indicates whether the login is locked.
IsPasswordExpired bit Indicates whether the login is expired.
IsSystemObject bit Indicates whether the login is a system object.
LanguageAlias varchar Default language for this login.
MustChangePassword bit Indicates whether the login must change passwords the next time it connects.
PasswordExpirationEnabled bit Indicates whether password expiration is checked.
PasswordPolicyEnforced bit Indicates whether password policy is checked.
State varchar Login state. When first collected, the password state is ‘Existing’.
WindowsLoginAccessType varchar For Windows logins, the access type.
 
If a login is not a Windows login, value is ‘NonNTLogin’.
If a Windows login is a Windows login, value can be ‘Undefined’, ‘Grant’, or ‘Deny’.
DefaultDatabase varchar The name of the default database for this login.
PrincipalID int ID number of the principal. 
DaysUntilExpiration int The number of days until the password expires.
 

*While sys.syslogins is marked for deprecation, it’s still alive and well in SQL Server 2016, and we have our reasons.
 

Collector.ServerPermissions

Contains collected server permissions information.

Definitions for many of the Collector.ServerPermissions columns can be found in the MSDN article for sys.server_permissions: https://msdn.microsoft.com/en-us/library/ms186260.aspx
 
Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
Class tinyint See the sys.server_permissions MSDN article.
ClassDesc nvarchar See the sys.server_permissions MSDN article.
MajorID int See the sys.server_permissions MSDN article.
MinorID int See the sys.server_permissions MSDN article.
GranteePrincipalID int See the sys.server_permissions MSDN article.
GrantorPrincipalID Int See the sys.server_permissions MSDN article.
Type char See the sys.server_permissions MSDN article.
PermissionName nvarchar See the sys.server_permissions MSDN article.
State char See the sys.server_permissions MSDN article.
StateDesc nvarchar See the sys.server_permissions MSDN article.
 

Collector.Endpoints

Contains collected server endpoint information.

Note that while endpoints don’t have much to do with logins directly, logins can have permissions to endpoints. So, Minion Enterprise collects a sort of “snapshot” of server security all at once.
Definitions for many of the Collector.Endpoints columns can be found in the MSDN article for sys.endpoints: https://msdn.microsoft.com/en-us/library/ms189746.aspx
 
Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
Name sysname See the sys.endpoints MSDN article.
EndpointID int See the sys.endpoints MSDN article.
PrincipalID int See the sys.endpoints MSDN article.
Protocol tinyint See the sys.endpoints MSDN article.
ProtocolDesc nvarchar See the sys.endpoints MSDN article.
Type tinyint See the sys.endpoints MSDN article.
TypeDesc nvarchar See the sys.endpoints MSDN article.
State tinyint See the sys.endpoints MSDN article.
StateDesc nvarchar See the sys.endpoints MSDN article.
IsAdminEndpoint bit See the sys.endpoints MSDN article.
 

Stored procedures

Stored procedures for the Login module include:
  • Alert.AdminRoleLoginNewRetired
  • Audit.LoginPermAccessMethod
  • Report.LoginADGroupMembershipByLogin
  • Report.LoginCountsOnServers

Other procedures not shown include those for related modules and utilities (e.g., the Login Password Strength Utility).
 

Alert.AdminRoleLoginNewRetired

This procedure is called automatically from jobs, to alert you to new and retired logins in high-level SQL Server roles.
 
Parameter Type Description
@EmailProfile varchar The name of the email profile to use for this alert.
@ServiceLevel varchar The service level to alert for.
 

Audit.LoginPermAccessMethod

You can use this procedure to see a user's access, and how the access is granted, across all instances of SQL Server. It returns a list of the servers the user has a login on, and the method that is used to grant it. For example, a user may have access through a SQL acct, or a Windows group. If it's a Windows group, then all the Windows groups that the user has access to the instance through will be listed.

This procedure is an excellent way to tell:
  • How many servers the user has rights on
  • If a user is given rights via multiple methods
  • Whether a user has both Active Directory and SQL accounts on the same box
 
Parameter Type Description
@Acct varchar The name of the account to report on. May be NULL.
@ServerName varchar The name of the server to report on. May be NULL.
 

Report.LoginADGroupMembershipByLogin

This procedure answers the question, "What servers does this AD account have access to, and via what AD groups?" It shows the chains of access that enable it to login to managed SQL Server instances. For example, user1 is a member of Group1, which is a member of Group2, which has access to Server1. So, the chain of inheritance is this: Server1 << Group2 << Group1 << user1.
Note that results become less accurate as either the AD group collection (in table Collector.ADGroupMember), or the logins collection (in table Collector.Logins) age.
 
Parameter Type Description
@LoginName nvarchar The name of the login to report on.
 

Report.LoginCountsOnServers

This procedure returns a list that includes all logins, their types, and their associated servers.
This procedure is called from the SSRS report SALoginCountPerServer.
 
Parameter Type Description
@ServerList varchar Comma-delimited list of servers, or NULL (all).
 

Views

Collector.LoginsCurrent

Provides the most recent collection of login data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr

Collector.LoginsPrevious

Provides the next-to-most recent collection of login data.

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Collector.EndpointsCurrent

Provides the most recent collection of endpoint data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Collector.EndpointsPrevious

Provides the next-to-most recent collection of endpoint data.

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Collector.ServerPermissionsCurrent

Provides the most recent collection of server permissions data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Collector.ServerPermisionsPrevious

Provides the next-to-most recent collection of server permissions data.

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr

 

Jobs

CollectorLoginsGet-GOLD

Calls the executable SQLLoginsGet.exe for all servers with ServiceLevel = ‘Gold’, to retrieve table information.
 

CollectorLoginsGet-SILVER

Calls the executable SQLLoginsGet.exe for all servers with ServiceLevel = ‘Silver’, to retrieve table information.
 

CollectorLoginsGet-BRONZE

Calls the executable SQLLoginsGet.exe for all servers with ServiceLevel = ‘Bronze’, to retrieve table information.
 

Executables

SQLLoginsGet.exe

Perform the login data collection for the servers in the specified service level. Log the results to Collector.Logins.

Input parameters:
  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.
 
Example execution:
C:\MinionByMidnightDBA\Collector\SQLLoginsGet.exe Gold