MSSQL Data Source Spectrum Reports¶

The reports for All User and Admin Count, User Grants and Privileges, Databases, User Privilege Report, Login List, and User Granted Role and Table Count can be generated for the MSSQL data sources.

The following permissions are required for the following MSSQL objects:

select on sys.database_principals
select on sys.database_permissions
select on sys.database_role_members
select on sys.sysdatabases
select on sys.dm_exec_sessions
select on sys.dm_exec_connections
select on sys.server_principals
select on sys.server_role_members
CONNECT ANY DATABASE

These permissions are necessary for the SQL queries in the fetch_report methods of the various classes within DatabaseRepo. The queries access system views and tables to retrieve user, role, privilege, login, and database information. These reports are explained in detail below.

All User and Admin Count¶

The report provides a summary of the number of admin and non-admin user accounts in an MSSQL database, along with details about the data source and a timestamp indicating when the data was recorded. It is useful for assessing the distribution of administrative and non-administrative users within the database.

User Grants and Privileges¶

The report provides information about the privileges and roles granted to various users within the 'master' database, including the type of grant and the timestamp when the data was recorded. It is useful for auditing and managing user permissions and roles within the SQL database.

Databases¶

The report lists the names of various databases within a SQL Server instance. It is useful for identifying and managing the different databases available on the server.

User Privilege Report¶

The report provides details about various users and roles within a SQL Server database, including the number of roles granted, user IDs, and any associated role names. It is useful for managing and auditing user permissions and roles in the database.

The report logs the login events for various users on an SQL Server, detailing their status, host information, client address, default database, program used, and data source details. It is useful for monitoring and auditing user activities and connections to the server.

User Granted Role and Table Count¶

The report provides information about user accounts in a MSSQL database including the number of roles granted to each user and the data source details. It is useful for tracking user privileges and understanding the structure of user roles within the database.

Server Role System Privileges Granted to User and Role¶

The report provides information about server-level roles and privileges granted to users and roles within a SQL Server instance.

It helps identify how high-level permissions are distributed across different accounts and role memberships in the environment. This report is useful for reviewing access assignments and understanding how server-level permissions are structured.

Role Granted to User and Role¶

The report provides information about roles granted to users and other roles within the SQL Server environment. It helps illustrate the role membership structure and how permissions may be inherited through role assignments. This report is useful for analyzing role relationships and reviewing how role-based access is organized within the database environment.

Server Role System Privileges Granted to User¶

The report provides information about server-level roles and privileges assigned directly to users. It helps identify users who may have broader access capabilities within the SQL Server instance. This report is useful for auditing permission assignments and reviewing how privileges are distributed among users.

DB Owner Users¶

The report provides information about users who have elevated privileges within specific databases. These users may have broader capabilities to manage database objects, configurations, and access permissions. This report is useful for identifying accounts with extensive database-level access and supporting database security reviews.

Srv Account of Sys Server Security Admin¶

The report provides information about server accounts that are associated with highly privileged server-level roles. These accounts may have broader administrative capabilities that allow them to manage server configuration, security settings, or database operations. This report helps administrators review privileged access and monitor accounts that have extensive control within the SQL Server environment.