# 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.

> ```sql
> 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.

> ```sql
> 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.

> ```sql
> # (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.

```sql
# (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:
    
    ```sql
    # (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

```sql
# 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.