Find out ENABLED GUEST user in all registered SQL Servers with HTML output via PowerShell

Use PowerShell to determine which registered SQL Servers have the ENABLED GUEST user with HTML output.

Tasks:

1. Extract the list of all registered SQL servers.
2. Get a T-SQL script ready.
3. Get the HTML and CSS style ready.
4. Combine them in PowerShell.

In most cases, locating a user within a SQL server is rather simple. The new system table or the conventional system table can be used.

sysusers
sys.database_principals

However, the user's enabled or disabled status, which is only available through the table, is: sys.sysusers. Let's get right to the coding.

1.Extract the list of all registered SQL servers

I utilized the SQL SSMS tool that was integrated into Central Management Servers. The server will be kept 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;

2. Get a T-SQL script ready

The script that follows will be used to traverse every SQL server and retrieve the user status, which is not equal to 0 (i.e., "ENABLED"). Because Powershell contains parameters with different formats, the final T-SQL script might differ 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;

3. Get the HTML and CSS style ready

The query result will be produced in HTML format, and if necessary, it may also be output as an attachment. Since the script will be wrapped in PowerShell and include a hardcoded string. Thus, it will appear in a tabular 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>'

4.Combine them in PowerShell

I am going to run a query against the target SQL server using the "invoke-sqlcmd" technique.

# (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…

Certain script statements can be stored as your own template and used again 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 $style -Property ServerName, DBName, status | out-string;