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