A little bit of POSH for document conversion


This is a completely non-SQL Server post. I had to recently convert a large number of word documents into Web Archive (.MHT) format. I would not mind doing that for one or two documents but when I have over 50 documents to perform this exercise on, it could get cumbersome and monotonous! This is where PowerShell came to the rescue.

The most common example available on the web is to convert Word documents to PDF. What I needed for the work that I was doing was a way to convert the Word documents to the Web Archive format. After a few Bing searches, I was able to determine that the Web Archive format enumeration number was 9. The following link has information about all the enumeration values: http://msdn.microsoft.com/en-us/library/office/bb238158(v=office.12).aspx

The PowerShell script below allows you to traverse a folder, pick all the word documents in the folders recursively and then convert each of those word documents in a .MHT file with the same name in the same location.

The script can be downloaded from OneDrive also.


<#

#################################################################################
    
Script Name: ConvertToWord                        
    Author: Amit Banerjee                            
    Date: April 28, 2014                            
    
Description:                                 
    This script takes a folder as an input and then converts the docx files present in the folder to web archive documents        
#################################################################################

This Sample Code is provided for the purpose of illustration only and is not 
intended to be used in a production environment. THIS SAMPLE CODE AND ANY 
RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER 
EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF 
MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a 
nonexclusive, royalty-free right to use and modify the Sample Code and to 
reproduce and distribute the object code form of the Sample Code, provided that 
You agree: (i) to not use Our name, logo, or trademarks to market Your software 
product in which the Sample Code is embedded; (ii) to include a valid copyright 
notice on Your software product in which the Sample Code is embedded; and (iii) 
to indemnify, hold harmless, and defend Us and Our suppliers from and against 
any claims or lawsuits, including attorneys fees, that arise or result from the 
use or distribution of the Sample Code.

    The enumeration for the various document types that you can save as from Microsoft Word when you use the SaveAs option
    Const wdFormatDocument                    =  0
    Const wdFormatDocument97                  =  0
    Const wdFormatDocumentDefault             = 16
    Const wdFormatDOSText                     =  4
    Const wdFormatDOSTextLineBreaks           =  5
    Const wdFormatEncodedText                 =  7
    Const wdFormatFilteredHTML                = 10
    Const wdFormatFlatXML                     = 19
    Const wdFormatFlatXMLMacroEnabled         = 20
    Const wdFormatFlatXMLTemplate             = 21
    Const wdFormatFlatXMLTemplateMacroEnabled = 22
    Const wdFormatHTML                        =  8
    Const wdFormatPDF                         = 17
    Const wdFormatRTF                         =  6
    Const wdFormatTemplate                    =  1
    Const wdFormatTemplate97                  =  1
    Const wdFormatText                        =  2
    Const wdFormatTextLineBreaks              =  3
    Const wdFormatUnicodeText                 =  7
    Const wdFormatWebArchive                  =  9
    Const wdFormatXML                         = 11
    Const wdFormatXMLDocument                 = 12
    Const wdFormatXMLDocumentMacroEnabled     = 13
    Const wdFormatXMLTemplate                 = 14
    Const wdFormatXMLTemplateMacroEnabled     = 15
    Const wdFormatXPS                         = 18

#>

# Replace with the correct folder path
# Remove the -recurse option if you only want to convert the documents in the first level folders
# Retrieve the list of documents
$Files = Get-ChildItem "C:\Windows\*.docx" -recurse 

foreach ($File in $Files)
{
    # Create the name of the new document
    $Name = $File.FullName.replace(“docx”,”mht”)
    
    if (Test-Path $Name)
    {
        # Check if the file already exists
        # If it does then do not do anything
        Write-Host "Skipping conversion for " $Name  
    }
    else 
    {
        # Save the file as a web archive if it does not exist
        Write-Host "Creating file " $Name
        ConvertToMHT $File.FullName
    }
}

# Function to convert the file
function ConvertToMHT ($FileName)
{        
    # Create a word document object
    $Word=NEW-OBJECT –COMOBJECT WORD.APPLICATION
    # Open the word document
    $Doc=$Word.Documents.Open($FileName)
            
    # Replace with appropriate document format type using the enumeration provided above in the comments
    # Save the document in the required format in the same location
    [ref]$SaveFormat = "System.Object" -as [type]
    $Doc.saveas([ref] (($FileName).replace(“docx”,”mht”)),  [ref]9)
    # Quit word after closing the document
    $Doc.close()
    $Word.Application.Quit()
}

Reference:
http://blogs.technet.com/b/heyscriptingguy/archive/2013/03/24/weekend-scripter-convert-word-documents-to-pdf-files-with-powershell.aspx

Advertisements

T-SQL Tuesday #50: Database Role Membership


imageFirst of all, a Happy New Year to everyone!

This month’s T-SQL Tuesday topic on Automation provides a perfect opportunity for me to blog about an automation that can be achieved around database roles and it’s members.

A big thank you to Hemanth [T | B] for hosting this year’s first T-SQL Tuesday and to Adam Machanic [B|T] as his initiative just reached a milestone! 50 months filled with awesome SQL related blog posts on a Tuesday!

The ask was to create a database role matrix for each and every user database on the SQL Server instance. Now the catch here was to find out memberships in custom database roles as well.

So here is how to go about this.

First task would be to identify each and every fixed database role that a particular database user is part of. This is a fairly simple task as there are system catalogs to fetch this information.

The next task is to find out each and every custom database role that exists in each user database. This can be achieved using the T-SQL script below.

<span style="font-size: small;">
/*
# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: T-SQL script to fetch all the custom roles in a database
*/
DECLARE @ctr int = 1, @maxctr int = 1, @dbname sysname, @rsql nvarchar(4000)

CREATE TABLE #tblCustomRoles (rolename varchar(255))

SELECT identity(int,1,1) as databaseid, name
INTO #dbs
FROM sys.databases
WHERE database_id NOT IN (1,2,3,4)
AND state_desc = 'ONLINE'

SELECT @maxctr = COUNT(*) FROM #dbs
WHILE @ctr <= @maxctr
BEGIN
SELECT @dbname = name FROM #dbs WHERE databaseid = @ctr
SET @rsql = 'SELECT name FROM ' + @dbname +'.sys.database_principals where type_desc = ''DATABASE_ROLE'' and is_fixed_role = 0 and name <> ''public'''
INSERT INTO #tblCustomRoles
EXEC sp_executesql @rsql
SET @ctr += 1
END

SELECT DISTINCT ', CASE IS_MEMBER ('''+rolename+''') WHEN 1 THEN ''YES'' ELSE ''NO'' END as ' + quotename(rolename) as sqlstring
FROM #tblCustomRoles

DROP TABLE #tblCustomRoles
DROP TABLE #dbs


Now that you have list of custom database roles, you need to create a T-SQL query with the custom database role list and the fixed database role list. This query when executed against each user database will give you the database role matrix that you are looking for.

And this is where an automation scripter’s best friend, Powershell steps in. The script below:

1. Fetches the custom database roles from each database

2. Creates the T-SQL query for fetching the database role members

3. Executes the query against each database except for master, msdb, model and tempdb

4. Appends the output from each database into a CSV file


# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: Powershell script to fetch role membership for each database user in all databases in a single SQL Server instance
# Usage: .\GetPerms.ps1 -vFileName "<Output file name with fully qualified path>" -vServer "<SQL Server Instance Name>" -vScriptFile "<Location of GetCustomRoles.sql>"

Param ([string] $vFileName, [string] $vServer, [string] $vScriptFile)

# Declaring variables
$VCustomRoles = ""

# Fetch the names of the databases
$vDatabases = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -Query "select name from sys.databases where database_id not in (1,2,3,4) and state_desc = 'ONLINE'"

# Find non-system database roles
$vRoles = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -InputFile $vScriptFile

# Construct the SQL query to be executed
$vQuery = "SELECT
db_name() as DatabaseName
, prin.name as DBUserName
, srvprin.name as LoginName
, srvprin.create_date
, CASE IS_MEMBER ('db_owner') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_owner]
, CASE IS_MEMBER ('db_accessadmin') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_accessadmin]
, CASE IS_MEMBER ('db_securityadmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_securityadmin]
, CASE IS_MEMBER ('db_ddladmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_ddladmin]
, CASE IS_MEMBER ('db_backupoperator')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_backupoperator]
, CASE IS_MEMBER ('db_datareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datareader]
, CASE IS_MEMBER ('db_datawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datawriter]
, CASE IS_MEMBER ('db_denydatareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatareader]
, CASE IS_MEMBER ('db_denydatawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatawriter]
, CASE IS_MEMBER ('public')  WHEN 1 THEN 'YES' ELSE 'NO' END as [public]
"

# Foreach loop to identify each custom role in all the databases on the instance
foreach ($vRole in $vRoles)
{
$vQuery = $vQuery + $vRole.sqlstring + "`n"
}

# Complete the T-SQL query to be executed
$vQuery = $vQuery + "FROM sys.database_role_members mem
INNER JOIN sys.database_principals prin on mem.member_principal_id = prin.principal_id
INNER JOIN sys.database_principals rol on rol.principal_id = mem.role_principal_id
LEFT OUTER JOIN sys.server_principals srvprin on srvprin.sid = prin.sid
WHERE prin.is_fixed_role = 0 and srvprin.type in ('U','S')"

# Foreach loop to fetch the role membership of each user and append to a CSV file
foreach ($db in $vDatabases)
{
"Fetching database role matrix for database: " + $db.name
# NoTypeInformation prevents the #TYPE System.Data.DataRow from being the first row in the CSV file
# Use Invoke-SQLCMD to get the database role matrix for each database and append it to an output CSV file
Invoke-Sqlcmd -ServerInstance $vServer -Database $db.name -Query $vQuery | Export-Csv -Path $vFilename -Append -NoClobber -Encoding UTF8 -ErrorAction Stop -NoTypeInformation
}

The CSV output file shows a neat matrix as seen in the screenshot below:

image

Example command:

.\GetPerms.ps1 -vFilename G:\Perms.CSV -vServer MYSERVER -vScriptFile “G:\GetCustomRoles.sql”

Happy Automation!