Automation help

Question

Need some one help-
In the above script there are 8 scripts(ServerLogins, Server Role membership, Server Roles available, Server Certificate Principal, Database Login, Database Certificate Principals, Database Role membership, Database Roles available) to pull the results, currently its coming in one html using servers.txt(all servers included)

1.All servers results in an separate html ex- like ServerLogins Results for all servers in one html, similarly for other (means total 8 html files).

2.Also there are some servers that uses sql server service accounts even that also need include in same script.

3.Need to get a log total servers results is pulled success information’s and fails if any then logging in to the txt in the drive.

Please check the script used-
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Function Execute_SQLQuery ($Query, $ConString ) {

$sourceConnection = New-Object System.Data.SqlClient.SQLConnection($ConString)
$sourceConnection.open()
$Da = new-object System.Data.SqlClient.SqlDataAdapter ($Query, $sourceConnection)
$DataTable = new-object System.Data.DataTable
$da.fill( $DataTable )
$sourceConnection.close()
return $DataTable
}

defin script object

$myitems =
@(
[pscustomobject]@{
table_head= "Server Role membership";
script="SET nocount on
SELECT @@servername as 'ServerName', sPrinc.name AS [ServerPrincipals], sRole.name AS [ServerRoleName]
FROM sys.server_role_members AS sRo
JOIN sys.server_principals AS sPrinc
ON sRo.member_principal_id = sPrinc.principal_id
JOIN sys.server_principals AS sRole
ON sRo.role_principal_id = sRole.principal_id";
Columns=@('ServerName', 'ServerPrincipals', 'ServerRoleName')
},

[pscustomobject]@{
table_head= "Server Roles available";
script=" select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType
from sys.server_principals where type_desc='SERVER_ROLE'" ;
Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType')},

[pscustomobject]@{
table_head= "Server Logins";
script="select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType
,is_disabled as IsPrincipalDisabled from sys.server_principals
where type_desc in ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')";
Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType','IsPrincipalDisabled')},

[pscustomobject]@{
table_head= "Server Certificate PrincipalsServer Certificate Principals";
script="select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType
from sys.server_principals where type_desc='CERTIFICATE_MAPPED_LOGIN'";
Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType')},

[pscustomobject]@{
table_head= "Database Login";
script="DECLARE @SQLStatement VARCHAR(4000)
DECLARE @T_DBuser TABLE
(
DBName sysname, name sysname, principal_id int, type char(1), type_desc nvarchar(60), default_schema_name sysname null,
create_date datetime, modify_date datetime, owning_principal_id int, sid varbinary(85), is_fixed_role bit
)

SET @SQLStatement='use [?]
SELECT db_name() as DBName,
name , principal_id , type , type_desc , default_schema_name ,
create_date , modify_date , owning_principal_id , sid , is_fixed_role
FROM sys.database_principals ORDER BY principal_id

'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,
type_desc as DatabasePrincipalType
FROM @T_DBuser --ORDER BY DBName
where type_desc in ('SQL_USER','WINDOWS_GROUP','WINDOWS_USER')";
Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')},

[pscustomobject]@{
table_head= "Database Certificate Principals";
script="DECLARE @SQLStatement VARCHAR(4000)
DECLARE @T_DBuser TABLE
(
DBName sysname, name sysname, principal_id int, type char(1), type_desc nvarchar(60), default_schema_name sysname null,
create_date datetime, modify_date datetime, owning_principal_id int, sid varbinary(85), is_fixed_role bit
)

SET @SQLStatement='use [?]
SELECT db_name() as DBName,
name , principal_id , type , type_desc , default_schema_name ,
create_date , modify_date , owning_principal_id , sid , is_fixed_role
FROM sys.database_principals ORDER BY principal_id

'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,
type_desc as DatabasePrincipalType
FROM @T_DBuser --ORDER BY DBName
where type_desc ='CERTIFICATE_MAPPED_USER'";
Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')},

[pscustomobject]@{
table_head= "Database Role memberships";
script="DECLARE @SQLStatement VARCHAR(4000)

DECLARE @T_DBuser TABLE
(
ServerName sysname,
DBName SYSNAME,
UserName SYSNAME,
Permission NVARCHAR(256),
Permission_Database_ObjectLevel NVARCHAR(256),
State_Desc NVARCHAR(256) ,
AssociatedDBRole NVARCHAR(256)
)

SET @SQLStatement='use ?
SELECT @@SERVERNAME ServerName, DB_NAME()DBName, pr.name , pe.permission_name, ISNULL(OBJECT_NAME(pe.major_id),
DB_NAME()) Permission_Database_ObjectLevel, pe.state_desc, USER_NAME(rm.role_principal_id) [database_role]
FROM sys.database_principals pr
LEFT JOIN sys.database_permissions pe ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.database_role_members rm ON pe.grantee_principal_id = rm.member_principal_id
where type_desc<>''DATABASE_ROLE''
'

INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement

SELECT * FROM @T_DBuser
ORDER BY DBName";
Columns=@('ServerName', 'DBName', 'UserName','Permission', 'Permission_Database_ObjectLevel','State_Desc','AssociatedDBRole' )},

[pscustomobject]@{
table_head= "Database Roles available";
script="DECLARE @SQLStatement VARCHAR(4000)
DECLARE @T_DBuser TABLE
(
DBName sysname, name sysname, principal_id int, type char(1), type_desc nvarchar(60), default_schema_name sysname null,
create_date datetime, modify_date datetime, owning_principal_id int, sid varbinary(85), is_fixed_role bit
)

SET @SQLStatement='use [?]
SELECT db_name() as DBName,
name , principal_id , type , type_desc , default_schema_name ,
create_date , modify_date , owning_principal_id , sid , is_fixed_role
FROM sys.database_principals ORDER BY principal_id
'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,
type_desc as DatabasePrincipalType
FROM @T_DBuser --ORDER BY DBName
where type_desc ='DATABASE_ROLE'";
Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')}

)

$header = '
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
h1 {

    font-family: Arial, Helvetica, sans-serif;  
    color: red;  
    font-size: 28px;  

}  


h2 {  

    font-family: Arial, Helvetica, sans-serif;  
    color: #000099;  
    font-size: 16px;  

}  

table {
font-size: 12px;
border: 0px;
font-family: Arial, Helvetica, sans-serif;
}

td {  
         padding: 4px;  
         margin: 0px;  
         border: 0;  
   }  

th {  
    background: #395870;  
    background: linear-gradient(#49708f, #293f50);  
    color: #ff9999;  
    font-size: 11px;  
    text-transform: uppercase;  
    padding: 10px 15px;  
    vertical-align: middle;  
   }  

tbody tr:nth-child(even) {  
    background: #f0f0f2;  
}  

    #CreationDate {  

    font-family: Arial, Helvetica, sans-serif;  
    color: #ff3300;  
    font-size: 12px;  

}  

.collapsible {
background-color: #777;
color: white;
cursor: pointer;
padding: 18px;
width: 100%;
border: none;
text-align: left;
outline: none;
font-size: 15px;
}

.active, .collapsible:hover {
background-color: #555;
}

.collapsible:after {
content: "\002B";
color: white;
font-weight: bold;
float: right;
margin-left: 5px;
}

.active:after {
content: "\2212";
}

.content {
padding: 0 18px;
max-height: 0;
overflow: hidden;
transition: max-height 0.2s ease-out;
background-color: #f1f1f1;
}
</style>
</head>
<body>
'

$header | Out-File ONE.HTML

"<h2>SQL Server Audit report<h2>" + "<p>Creation Date: $(Get-Date)</p>" | Out-File ONE.HTML -Append
foreach ($myscript in $myitems) {
'<button type="button" class="collapsible">' + $myscript.table_head + ' </button>
<div class="content">' | Out-File ONE.HTML -Append

$Servers = Get-content -Path 'F:\DBA\UER_N\servers.txt'
foreach ($SQLServer in $Servers) {
$ConnectionString = "server=$SQLServer;Trusted_Connection=true;Initial Catalog=msdb;Integrated Security=True"
Execute_SQLQuery $myscript.script $ConnectionString | select $myscript.Columns | convertTO-HTML -Fragment | Out-File ONE.HTML -Append
'</div>' | Out-File ONE.HTML -Append
}
}

'<script>
var coll = document.getElementsByClassName("collapsible");
var i;

for (i = 0; i < coll.length; i++) {
coll[i].addEventListener("click", function() {
this.classList.toggle("active");
var content = this.nextElementSibling;
if (content.style.maxHeight){
content.style.maxHeight = null;
} else {
content.style.maxHeight = content.scrollHeight + "px";
}
});
}
</script>
</body>
</html>
' | Out-File ONE.HTML -Append

Answer 1

Hi，

To create one file for each script you just need to output to different files. If you want to write to a log file when errors occur you can add try/catch blocks.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")   
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")   
Function Execute_SQLQuery ($Query, $ConString )  {  
  
$sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($ConString)  
$sourceConnection.open()  
$Da = new-object System.Data.SqlClient.SqlDataAdapter ($Query, $sourceConnection)  
   $DataTable = new-object System.Data.DataTable  
  $da.fill( $DataTable )    
$sourceConnection.close()  
return $DataTable  
}  
$myitems =  
@(  
[pscustomobject]@{  
    table_head= "Server Role membership";  
    script="SET nocount on  
        SELECT @@servername as 'ServerName', sPrinc.name AS [ServerPrincipals], sRole.name AS [ServerRoleName]  
        FROM sys.server_role_members AS sRo  
        JOIN sys.server_principals AS sPrinc  
        ON sRo.member_principal_id = sPrinc.principal_id  
        JOIN sys.server_principals AS sRole  
        ON sRo.role_principal_id = sRole.principal_id";  
    Columns=@('ServerName', 'ServerPrincipals', 'ServerRoleName')  
    },  
  
[pscustomobject]@{  
    table_head= "Server Roles available";  
    script=" select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType  
                from sys.server_principals where type_desc='SERVER_ROLE'" ;  
   Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType')},  
  
[pscustomobject]@{  
        table_head= "Server Logins";  
        script="select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType  
            ,is_disabled as IsPrincipalDisabled from sys.server_principals  
            where type_desc in ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')";  
        Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType','IsPrincipalDisabled')},  
  
[pscustomobject]@{  
        table_head= "Server Certificate PrincipalsServer Certificate Principals";  
        script="select @@ServerName as ServerName,name as ServerPrincipal,type_desc as PrincipalType  
                    from sys.server_principals where type_desc='CERTIFICATE_MAPPED_LOGIN'";  
       Columns=@('ServerName', 'ServerPrincipal', 'PrincipalType')},  
  
[pscustomobject]@{  
        table_head= "Database Login";  
        script="DECLARE @SQLStatement VARCHAR(4000)   
DECLARE @T_DBuser TABLE   
(  
DBName sysname, name sysname,      principal_id int,    type char(1),      type_desc nvarchar(60),     default_schema_name sysname null,   
create_date   datetime, modify_date datetime,    owning_principal_id int,   sid varbinary(85),   is_fixed_role bit  
)   
  
SET @SQLStatement='use [?]  
SELECT db_name() as DBName,   
name , principal_id ,       type , type_desc ,   default_schema_name ,        
create_date   , modify_date ,      owning_principal_id ,       sid ,       is_fixed_role   
FROM sys.database_principals ORDER BY principal_id  
  
'  
INSERT @T_DBuser  
EXEC sp_MSforeachdb @SQLStatement  
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,   
type_desc as DatabasePrincipalType  
FROM @T_DBuser --ORDER BY DBName  
where type_desc in ('SQL_USER','WINDOWS_GROUP','WINDOWS_USER')";  
        Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')},  
  
[pscustomobject]@{  
        table_head= "Database Certificate Principals";  
        script="DECLARE @SQLStatement VARCHAR(4000)   
DECLARE @T_DBuser TABLE   
(  
DBName sysname, name sysname,      principal_id int,    type char(1),      type_desc nvarchar(60),     default_schema_name sysname null,   
create_date   datetime, modify_date datetime,    owning_principal_id int,   sid varbinary(85),   is_fixed_role bit  
)   
  
SET @SQLStatement='use [?]  
SELECT db_name() as DBName,  
name , principal_id ,       type , type_desc ,   default_schema_name ,        
create_date   , modify_date ,      owning_principal_id ,       sid ,       is_fixed_role   
FROM sys.database_principals ORDER BY principal_id  
  
'  
INSERT @T_DBuser  
EXEC sp_MSforeachdb @SQLStatement  
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,   
type_desc as DatabasePrincipalType  
FROM @T_DBuser --ORDER BY DBName  
where type_desc ='CERTIFICATE_MAPPED_USER'";  
        Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')},  
  
[pscustomobject]@{  
        table_head= "Database Role memberships";  
        script="DECLARE @SQLStatement VARCHAR(4000)   
  
DECLARE @T_DBuser TABLE   
(  
ServerName sysname,  
DBName SYSNAME,   
UserName SYSNAME,   
Permission NVARCHAR(256),  
Permission_Database_ObjectLevel NVARCHAR(256),  
State_Desc NVARCHAR(256) ,   
AssociatedDBRole NVARCHAR(256)  
)   
  
SET @SQLStatement='use ?  
SELECT @@SERVERNAME ServerName, DB_NAME()DBName, pr.name , pe.permission_name, ISNULL(OBJECT_NAME(pe.major_id),   
DB_NAME()) Permission_Database_ObjectLevel, pe.state_desc, USER_NAME(rm.role_principal_id) [database_role]  
FROM sys.database_principals pr   
LEFT JOIN sys.database_permissions pe ON pe.grantee_principal_id = pr.principal_id  
LEFT JOIN sys.database_role_members rm ON pe.grantee_principal_id = rm.member_principal_id  
where type_desc<>''DATABASE_ROLE''  
'  
  
INSERT @T_DBuser  
EXEC sp_MSforeachdb @SQLStatement  
  
SELECT * FROM @T_DBuser   
ORDER BY DBName";  
        Columns=@('ServerName', 'DBName', 'UserName','Permission', 'Permission_Database_ObjectLevel','State_Desc','AssociatedDBRole' )},  
  
[pscustomobject]@{  
        table_head= "Database Roles available";  
        script="DECLARE @SQLStatement VARCHAR(4000)   
DECLARE @T_DBuser TABLE   
(  
DBName sysname, name sysname,      principal_id int,    type char(1),      type_desc nvarchar(60),     default_schema_name sysname null,   
create_date   datetime, modify_date datetime,    owning_principal_id int,   sid varbinary(85),   is_fixed_role bit  
)   
  
SET @SQLStatement='use [?]  
SELECT db_name() as DBName,  
name , principal_id ,       type , type_desc ,   default_schema_name ,        
create_date   , modify_date ,      owning_principal_id ,       sid ,       is_fixed_role   
FROM sys.database_principals ORDER BY principal_id  
'  
INSERT @T_DBuser  
EXEC sp_MSforeachdb @SQLStatement  
SELECT @@SERVERNAME as ServerName,DBName,name as DatabasePrincipal,   
type_desc as DatabasePrincipalType  
FROM @T_DBuser --ORDER BY DBName  
where type_desc ='DATABASE_ROLE'";  
        Columns=@('ServerName', 'DBName', 'DatabasePrincipal','DatabasePrincipalType')}  
  
  
)  
$header = '  
<!DOCTYPE html>  
<html>  
<head>  
<meta name="viewport" content="width=device-width, initial-scale=1">  
<style>  
    h1 {  
  
        font-family: Arial, Helvetica, sans-serif;  
        color: red;  
        font-size: 28px;  
  
    }  
  
      
    h2 {  
  
        font-family: Arial, Helvetica, sans-serif;  
        color: #000099;  
        font-size: 16px;  
  
    }  
  
      
      
   table {  
             font-size: 12px;  
             border: 0px;   
             font-family: Arial, Helvetica, sans-serif;  
       }   
         
    td {  
             padding: 4px;  
             margin: 0px;  
             border: 0;  
       }  
         
    th {  
        background: #395870;  
        background: linear-gradient(#49708f, #293f50);  
        color: #ff9999;  
        font-size: 11px;  
        text-transform: uppercase;  
        padding: 10px 15px;  
        vertical-align: middle;  
       }  
  
    tbody tr:nth-child(even) {  
        background: #f0f0f2;  
    }  
  
        #CreationDate {  
  
        font-family: Arial, Helvetica, sans-serif;  
        color: #ff3300;  
        font-size: 12px;  
  
    }  
      
.collapsible {  
  background-color: #777;  
  color: white;  
  cursor: pointer;  
  padding: 18px;  
  width: 100%;  
  border: none;  
  text-align: left;  
  outline: none;  
  font-size: 15px;  
}  
  
.active, .collapsible:hover {  
  background-color: #555;  
}  
  
.collapsible:after {  
  content: "\002B";  
  color: white;  
  font-weight: bold;  
  float: right;  
  margin-left: 5px;  
}  
  
.active:after {  
  content: "\2212";  
}  
  
.content {  
  padding: 0 18px;  
  max-height: 0;  
  overflow: hidden;  
  transition: max-height 0.2s ease-out;  
  background-color: #f1f1f1;  
}  
</style>  
</head>  
<body>  
'  
$Servers = Get-content -Path 'F:\DBA\UER_N\servers.txt'  
foreach ($myscript in $myitems) {  
    $header | Out-File "$($myscript.table_head).HTML"  
    "<h2>SQL Server Audit report<h2>" +  "<p>Creation Date: $(Get-Date)</p>"   | Out-File "$($myscript.table_head).HTML" -Append   
    '<button type="button" class="collapsible">' + $myscript.table_head + ' </button>  
    <div class="content">' | Out-File "$($myscript.table_head).HTML" -Append  
    foreach ($SQLServer in $Servers) {  
        $ConnectionString = "server=$SQLServer;Trusted_Connection=true;Initial Catalog=msdb;Integrated Security=True"  
        try{  
            Execute_SQLQuery $myscript.script $ConnectionString | select $myscript.Columns | convertTO-HTML -Fragment | Out-File "$($myscript.table_head).HTML" -Append  
            '{0}  {1}  SUCCEED' -f $myscript.table_head,$SQLServer | out-file log.txt -Append  
        }  
        catch{  
            '{0}  {1}  FAIL' -f $myscript.table_head,$SQLServer | out-file log.txt -Append      
        }  
        '</div>'  | Out-File "$($myscript.table_head).HTML" -Append  
    }  
    '<script>  
var coll = document.getElementsByClassName("collapsible");  
var i;  
  
for (i = 0; i < coll.length; i++) {  
  coll[i].addEventListener("click", function() {  
    this.classList.toggle("active");  
    var content = this.nextElementSibling;  
    if (content.style.maxHeight){  
      content.style.maxHeight = null;  
    } else {  
      content.style.maxHeight = content.scrollHeight + "px";  
    }   
  });  
}  
</script>  
</body>  
</html>  
' |  Out-File "$($myscript.table_head).HTML" -Append  
}  

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.