The Database Users module does more than just gather database user information. This collection is meant to serve a higher purpose – specifically, of providing a snapshot of database level security – and so, database users collection gathers information on:
  • Database roles
  • Role members
  • Database permissions
  • Symmetric keys
  • Asymmetric keys
  • Database certificates 

The Database Users module – it will be no surprise – also collects database user information from managed servers. This gives you a record of all database users – and their create date, last modified date, and more – for the entire enterprise.

This module collects data for several tables:
  • Collector.DBUsers
  • Collector.DatabasePrincipals
  • Collector.DBRoleMembers
  • Collector.DBPermissions
  • Collector.DBSymmetricKeys
  • Collector.DBAsymmetricKeys
  • Collector.CertificateDatabase 

The Collector.DBUsersCurrent view provides the latest collection of database users. Use it, together with the Collector.DBUsersPrevious view, to discover when users are added and deleted from specific databases.
 

Tables

Collector.DBUsers

Column Type Description
ID int Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID Int The instance ID of the instance in question, as defined in the table dbo.Servers.
DBName varchar Database name.
UserName varchar User name.
CreateDate datetime Date the account was added.
DateLastModified datetime Date the account was last changed.
DefaultLanguage varchar The default language configured for this account.
DefaultSchema varchar The schema name to be used when one is not specified.
HasDBAccess bit Whether the account has database access.
IsSystemObject bit Not currently in use.
Login varchar Name of the account (as defined in sys.server_principles).
LoginType varchar The type of login. Valid values include:
 
ApplicationRole
Certificate
Azure Active Directory User
DatabaseRole
SqlUser
WindowsUser
WindowsGroup
AsymmetricKey
Azure Active Directory Group
 
For more information, see the “type” field in the sys.server_principals article (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql).
SID varchar Security identifier of the account.
State varchar The state of the account; e.g., Existing.
 

Collector.DatabasePrincipals

The data in this table reflects the data available in the system table sys.database_principals on your instances. For more information, see the sys.database_principals article: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql.
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.
DBName varchar Database name.
Name varchar Database principal (user) name.
PrincipalID int ID of the user, unique within the database.
Type char Principal type, e.g. A, S, etc.
TypeDesc nvarchar Principal type description, e.g. APPLICATION_ROLE, SQL_USER, etc.
DefaultSchemaName varchar The schema name to be used when one is not specified.
CreateDate datetime Date the account was added.
ModifyDate datetime Date the account was last changed.
OwningPrincipalID int ID of the principal that owns this principal.
Sid varbinary Security identifier.
IsFixedRole bit If 1, the row is an entry for one of the fixed database roles (e.g. db_owner, etc.)
AuthenticaionType int Authentication type, e.g. 0, 1, etc.
AuthenticaionTypeDesc nvarchar Authentication type description, e.g. no authentication, instance authentication, etc.
DefaultLanguageName varchar The default language for this principal.
DefaultLanguageLCID int The default LCID (locale identifier) for this principal.


Collector.DBRoleMembers

This table holds database role members. For more information, see the Microsoft article on sys.database_role_members https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql
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.
DBName varchar Database name.
Name varchar Name of the principal (member).
RoleName varchar Name of the role.
RolePrincipalID int ID of the role.
MemberPrincipalID int ID of the principal (member).


Collector.DBPermissions

This table holds database permissions. For more information, see the Microsoft article on sys.database_permissions https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql
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.
DBName varchar Database name.
class tinyint The class on which the permission exists, e.g. 0, 1, etc.
class_desc nvarchar The description of the class on which the permission exists, e.g. DATABASE, OBJECT-OR-COLUMN, etc.
major_id int The id of thing on which permission exists. If “class” is 0, major_id will be 0; if “class” is 1, major_id is the object id.
minor_id int The secondary id of thing on which permission exists.
 
If “class” is 0, or the thing is an object, minor_id will be 0.
If “class” is 1 and the object is a colum, minor_id is 1.
grantee_principal_id int Database principal ID to which the permissions are granted.
grantor_principal_id int The database principal id of the permissions grantor.
type char Database permission types, e.g. CO, CRTB.
permission_name nvarchar Database permission name, e.g. CONNECT, CREATE TABLE.
state char Permission state; e.g., D, R, G, etc.
state_desc nvarchar Permission state description; e.g., DENY, REVOKE, GRANT, etc.
 

Collector.DBSymmetricKeys

This table holds symmetric keys. For more information, see the Microsoft article on sys.symmetric_keys https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-symmetric-keys-transact-sql 

 

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.
DBName varchar Database name.
name sysname Key name.
principal_id int ID of the databae principal who owns the key.
symmetric_key_id int Key ID, unique within the database.
key_length int Key length, in bits.
key_algorithm char Algorithm used with the key; e.g., R2, R4, etc.
algorithm_desc nvarchar Description of the algorithm used with the key; e.g., RC2, RC4, etc.
create_date datetime Key creation date.
modify_date datetime Date the key was last modified.
key_guid uniqueidentifier Unique identifier associated with the key. Globally unique.
key_thumbprint sql_variant SHA-1 hash of the key. Globally unique.
provider_type nvarchar Type of cryptographic provider. Valid values include “CRYPTOGRAPHIC PROVIDER” (for extensible management keys), and NULL for non-extensible key management keys.
cryptographic_provider_guid uniqueidentifier Unique identifier for the cryptographic provider.
cryptographic_provider_algid sql_variant Algorighm ID for the cryptographic provider.


Collector.DBAsymmetricKeys

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.
DBName varchar Database name.
name sysname Key name.
principal_id int ID of the databae principal who owns the key.
asymmetric_key_id int Key ID, unique within the database.
pvt_key_encryption_type char How the key is encrypted; e.g., NA, MK, etc.
pvt_key_encryption_type_desc nvarchar Description of how the key is encrypted; e.g., NO_PRIVATE_KEY, ENCRYPTED_BY_MASTER_KEY, etc.
thumbprint varbinary SHA-1 hash of the key. Globally unique.
algorithm char Algorithm used with the key, e.g. 1R.
algorithm_desc nvarchar Description of the algorithm used with the key, e.g. RSA_512.
key_length int Bit length of the key.
sid varbinary Login SID for this key.
string_sid nvarchar String representation of the login SID of the key.
public_key varbinary Public key.
attested_by nvarchar Microsoft use only.
 

Collector.CertificateDatabase

The data in this table reflects the data available in the system table sys.certificates on your instances. For more information, see the sys.certificates article: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-certificates-transact-sql.
 
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 int The instance ID of the instance in question, as defined in the table dbo.Servers.
DBName varchar Database name.
name sysname See the sys.certificates article on docs.Microsoft.com.
certificate_id int See the sys.certificates article on docs.Microsoft.com.
principal_id int See the sys.certificates article on docs.Microsoft.com.
pvt_key_encryption_type char See the sys.certificates article on docs.Microsoft.com.
pvt_key_encryption_type_desc nvarchar See the sys.certificates article on docs.Microsoft.com.
is_active_for_begin_dialog bit See the sys.certificates article on docs.Microsoft.com.
issuer_name nvarchar See the sys.certificates article on docs.Microsoft.com.
cert_serial_number nvarchar See the sys.certificates article on docs.Microsoft.com.
sid varbinary See the sys.certificates article on docs.Microsoft.com.
string_sid nvarchar See the sys.certificates article on docs.Microsoft.com.
subject nvarchar See the sys.certificates article on docs.Microsoft.com.
expiry_date datetime See the sys.certificates article on docs.Microsoft.com.
start_date datetime See the sys.certificates article on docs.Microsoft.com.
thumbprint varbinary See the sys.certificates article on docs.Microsoft.com.
attested_by nvarchar See the sys.certificates article on docs.Microsoft.com.
pvt_key_last_backup_date datetime See the sys.certificates article on docs.Microsoft.com.
 

Views

Minion Enterprise provides the following “current” and “previous” views for the Database Users Module:
  • Collector.DBUsersCurrent
  • Collector.DBUsersPrevious
  • Collector.DatabasePrincipalsCurrent
  • Collector.DatabasePrincipalsPrevious
  • Collector.DBRoleMembersCurrent
  • Collector.DBRoleMembersPrevious
  • Collector.DBPermissionsCurrent
  • Collector.DBPermissionsPrevious
  • Collector.DBSymmetricKeysCurrent
  • Collector.DBSymmetricKeysPrevious
  • Collector.DBASymmetricKeysCurrent
  • Collector.DBASymmetricKeysPrevious
  • Collector.CertificateDatabaseCurrent
  • Collector.CertificateDatabasePrevious
 

Jobs

CollectorDBUsersGet-GOLD

Calls the executable DBUsersGET.exe for all Gold level servers, to retrieve Database users membership information.
 

CollectorDBUsersGet-SILVER

Calls the executable DBUsersGET.exe for all Silver level servers, to retrieve Database users membership information.
 

CollectorDBUsersGet-BRONZE

Calls the executable DBUsersGET.exe for all Bronze level servers, to retrieve Database users membership information.
 

Executables

DBUsersGET.exe

Perform the database user data collection for managed servers. Log the results to tables:
  • Collector.DBUsers
  • Collector.DatabasePrincipals
  • Collector.DBRoleMembers
  • Collector.DBPermissions
  • Collector.DBSymmetricKeys
  • Collector.DBAsymmetricKeys
  • Collector.CertificateDatabase 

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\DBUsersGET.exe
 
 
https://minionware.desk.com/customer/portal/articles/2572237-database-users-module
http://www.MinionWare.net