Automation help

S_NO 21 Reputation points
2021-05-06T12:17:12.743+00:00

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,275 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,517 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 27,826 Reputation points Microsoft Vendor
    2021-05-07T08:41:39.98+00:00

    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.

    0 comments No comments