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

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

Tasks:

1. Get all registered SQL servers from list
2. Prepare T-SQL Script
3. Prepare HTML/CSS style
4. Put them together in Powershell

Typically, finding a user inside SQL server is pretty much a easy thing to do. You can either use the tranditional system table or the new system table.

sysusers
sys.database_principals

But the STATUS of the user, enabled or diabled that you can only get from the table: sys.sysusers

Let’s jump to the code directly.

1.Get all registered SQL servers from list

I used Central Management Servers feature that built in SQL SSMS tool. The server will be saved in the msdb system databse.

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. Prepare T-SQL Script

We are going to use this script below to navigate all SQl server, and then get the user status is not equal to 0 (which means “ENABLED“).
The final T-SQL Script would be a bit different due to parameter format in Powershell contetnt.

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. Prepare HTML/CSS style

It’s going to produce query result in HTML format, and could be output as an attachment if needed

Because it will be a string hard coded in PowerShell wrapped script. So it will look like below as a 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>'

4.Put them together

I will use “invoke-sqlcmd“ method to execute query against the target SQL server.

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

Some scripts statement could be reused for other cases, they can be saved as your own Template

• 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;