Uncovering Enabled Guest Users in SQL Servers: A PowerShell Guide with HTML Output

Use PowerShell to identify enabled Guest users in SQL Servers by extracting registered servers, preparing a T-SQL script, and formatting results in HTML. This method enhances security and provides a reusable template for database management.

Tasks:

1. Extract the list of all registered SQL servers: "Discovering Your SQL Server Landscape: Extracting Registered Servers"

2. Prepare a T-SQL Script: "Crafting the Perfect T-SQL Script for User Status Analysis"

3. Prepare the HTML and CSS Style: "Transforming Data into Insight: HTML and CSS Styling for SQL Results"

4. Combine Them in PowerShell: "PowerShell Magic: Integrating SQL Queries for Enhanced Security"

---

In most cases, locating a user within a SQL server is straightforward. You can use either the new system table or the traditional system table:

sysusers
sys.database_principals

However, determining whether a user is enabled or disabled is only possible through the sys.sysusers table. Let's dive into the coding process.

Extract the list of all registered SQL servers

I used the SQL SSMS tool integrated into the Central Management Servers. The server is stored in the MSDB system database of Microsoft.

SELECT s.[server_name] 
FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] g 
LEFT JOIN [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s 
ON      g.[server_group_id] = s.[server_group_id] 
WHERE g.[server_type]= 0 AND g.[is_system_object] = 0 
AND g.name = 'xxxxxxxx' --If you create folders for different servers;

Prepare a T-SQL Script

The following script will be used to go through each SQL server and get the user status that is not equal to 0 (meaning "ENABLED"). Since PowerShell uses parameters in different formats, the final T-SQL script may vary slightly.

DECLARE @DBName sysname;
CREATE TABLE #t1 (DBName sysname,[status] BIT)
DECLARE curDBName CURSOR FOR
SELECT name FROM master.sys.databases ORDER BY name;
OPEN curDBName;
FETCH NEXT FROM getDBName
INTO @DBName;
WHILE @@fetch_status=0 BEGIN
    INSERT INTO #t1 (DBName,[status])
    SELECT @DBName,[status] FROM sysusers WHERE name LIKE 'guest%'
    AND status <>0;
    FETCH NEXT FROM getDBName
    INTO @DBName;
END;
CLOSE curDBName;
DEALLOCATE curDBName;
SELECT @@servername as [ServerName], * FROM #t1;

Prepare the HTML and CSS Style

The query result will be generated in HTML format, and if needed, it can also be provided as an attachment. The script will be wrapped in PowerShell and include a hardcoded string, so it will be displayed in a table format below.

# (optional) Declare HTML CSS style for output format
$style = '<style>'
$style = $style + 'BODY{background-color:peachpuff;}'
$style = $style + 'TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}'
$style = $style + 'TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}'
$style = $style+ 'TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}'
$style = $style + '</style>'

Combine Them in PowerShell

I will run a query against the target SQL server using the invoke-sqlcmd method.

# (optional) Declare HTML CSS style for output format
$style = '<style>'
$style = $style + 'BODY{background-color:peachpuff;}'
$style = $style + 'TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}'
$style = $style + 'TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}'
$style = $style+ 'TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}'
$style = $style + '</style>'


$paramsAll = @{'server'='Server_FQDN_Name\xxInstanceName,1234_SQL_Port'; 'Database'='msdb'}
$Query1 = @"
SELECT s.[server_name] 
FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] g 
LEFT JOIN [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s 
ON       g.[server_group_id] = s.[server_group_id] 
WHERE  g.[server_type] = 0 AND g.[is_system_object] = 0 
AND g.name = 'xxxxxxxx' --Change as needed if you created folder
"@
$SrvAll = invoke-sqlcmd @paramsAll -Query $Query1

foreach ($svr in $SrvAll.server_name)
{
    if ($svr -ilike "*\MSSQLSERVER*")  {$svr = ($svr.split("\")[0])}

$sqlcmd1=[string]@"
DECLARE @DBName sysname;
CREATE TABLE #t1 (DBName sysname,[status] BIT)

DECLARE curDBName CURSOR FOR
SELECT name FROM master.sys.databases ORDER BY name;
OPEN curDBName;
FETCH NEXT FROM curDBName
INTO @DBName;

WHILE @@fetch_status=0 BEGIN

    INSERT INTO #t1 (DBName,[status])
    SELECT @DBName,[status] FROM sysusers WHERE name LIKE 'guest%'
    AND status <>0;

    FETCH NEXT FROM getDBName
    INTO @DBName;
END;

CLOSE curDBName;
DEALLOCATE curDBName;

SELECT '${svr}' as [ServerName], * FROM #t1; 
"@


$sqlcmd1Results = invoke-sqlcmd -ServerInstance $svr -Query $sqlCmd1 | convertTo-Html -Head $style -Property ServerName, DBName, status | out-string;

if (!$sqlcmd1Results){
    $sqlcmd1Results | Out-File -append "S:\path\GuestUser.html"
    }
    else
    {
    "No Result found" | Out-File -append "S:\path\GuestUser.html"
    }
}

---

more…

You can save certain script statements as your own template and reuse them in different situations.

• Table style:

    # (optional) Declare HTML CSS style for output format
    $style = '<style>'
    $style = $style + 'BODY{background-color:peachpuff;}'
    $style = $style + 'TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}'
    $style = $style + 'TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}'
    $style = $style+ 'TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}'
    $style = $style + '</style>'
  

Invoke-SQLCmd Output to HTML Format

# change -Property as needed based on output columns
$sqlcmd1Results = invoke-sqlcmd -ServerInstance $svr -Query $sqlCmd1 | convertTo-Html -Head $sty

In conclusion, using PowerShell to identify enabled guest users in SQL Servers with HTML output is a powerful and efficient method for database administrators.

By extracting the list of registered SQL servers, preparing a T-SQL script, and formatting the results in HTML, administrators can easily monitor and manage user access.

This approach not only enhances security by identifying enabled Guest users but also provides a reusable template for future audits and assessments.

The integration of PowerShell with SQL Server Management Studio and Central Management Servers streamlines the process, making it accessible and practical for ongoing database management tasks.