Getting Started with SQLPSX
** NOTE: You must have SMO installed to run the SQLPSX, SMO is installed with SQL Server Management Studio ***
1. Copy all Library and Script files to the same directory. Add sourcing of Library Files to
   your Profile if desired

Optional Database and Reporting Services Components
2. Create a database, for example SQLPSX and run the SQLPSX.AllObject.sql script to create the all database objects
3. Modify SSRS Data Source file SQLPSX.rds to point to the newly created database
4. Deploy the SSRS reports and Data Source files to a SSRS Server or run locally
5. Modify Run-SmoToCsvFile.ps1, Write-SmoCsvToDb.ps1 and Write-SmoToCsvFile.ps1 scripts with your parameters
6. Insert the SQL Server instances you wish to report into the SqlServer table
7. Run Run-SmoToCsvFile.ps1 to create csv files of all available security information
8. Run Write-SmoToCsvFile.ps1 to import the csv file into the database

What's New
    Version 1.4
        Added the following functions:
        Copy-ISItemSQLToSQL Copy-ISItemSQLToFile Copy-ISItemFileToSQL Get-ISData Get-ISItem Get-ISPackage Get-ISRunningPackage Get-ISSqlConfigurationItem
        New-ISApplication New-ISItem Remove-ISItem Rename-ISItem Set-ISConnectionString Set-ISPackage Test-ISPath
    Version 1.3
        Added the following functions:
        Get-SqlConnection Get-ReplServer Get-ReplLightPublication New-ReplTransPublication New-ReplMergePublication Get-ReplSubscriberSubscription
        Get-ReplPublication Get-ReplSubscription Get-ReplArticle Get-ReplMonitor Get-ReplPublisherMonitor Get-ReplPublicationMonitor
        Get-ReplEnumPublications Get-ReplEnumPublications2 Get-ReplEnumSubscriptions Get-ReplEnumSubscriptions2 Get-ReplTransPendingCommandInfo
        Get-ReplEnumLogReaderAgent Get-ReplEnumSnapshotAgent Set-ReplScriptOptions Get-ReplScript
    Version 1.2
        Added the following functions:
        Get-AgentJobServer  Get-AgentAlertCategory  Get-AgentAlert  Get-AgentJob  Get-AgentJobSchedule  Get-AgentJobStep  Get-AgentOperator
        Get-AgentOperatorCategory  Get-AgentProxyAccount  Get-AgentSchedule  Get-AgentTargetServerGroup  Get-AgentTargetServer  Get-AgentJobHistory
    Version 1.1
        Added the following functions:
        Get-SqlTable Get-SqlStoredProcedure Get-SqlView Get-SqlUserDefinedDataType Get-SqlUserDefinedFunction Get-SqlSynonym Get-SqlTrigger Get-SqlColumn
        Get-SqlIndex Get-SqlStatistic Get-SqlCheck Get-SqlForeignKey Set-SqlScriptingOptions Get-SqlScripter Get-Information_Schema.Tables
        Get-Information_Schema.Columns Get-Information_Schema.Views Get-Information_Schema.Routines Get-SysDatabases Get-SqlDataFile Get-SqlLogFile
        Get-SqlVersion Get-SqlPort ConvertTo-ExtendedPropertyXML Get-Sql ConvertTo-StatisticColumnXML ConvertTo-IndexedColumnXML

Libraries
    LibrarySmo.ps1 functions
        Get-SqlServer
            Returns a Microsoft.SqlServer.Management.Smo.Server Object
        Get-SqlDatabase
            Returns an SMO Database object or collection of Database objects
        Get-SqlData
            Executes a query returns an ADO.NET DataTable
        Set-SqlData
            Executes a query that does not return a result set
        Get-SqlShowMbrs
            Recursively enumerates AD/local groups handling built-in SQL Server Windows groups
        Get-SqlUser
            Returns a SMO User object with additional properties including all of the objects owned by the user
            and the effective members of the user. Recursively enumerates nested AD/local groups
        Get-SqlUserMember
            Helper function enumerates effective members of a user.
        Get-SqlDatabaseRole
            Returns a SMO DatabaseRole object with additional properties including the effective members of a
            role recursively enumerates nested roles, and users
        Get-SqlDatabaseRoleMember
            Helper function enumerates effective members of a role
        Get-SqlLogin
            Returns a SMO Login object with additional properties including the effective members of the login
        Get-SqlLinkedServerLogin
            Returns a SMO LinkedServerLogin object with additional properties including LinkedServer and DataSource
        Get-SqlLoginMember
            Helper function enumerates effective members of a login
        Get-SqlServerRole
            Returns a SMO ServerRole object with additional properties including the effective members of a role.
            Recursively enumerates nested AD/local groups
        Get-SqlServerPermission
            Returns a SMO ServerPermission object with additional properties including the effective members of a
            grantee. Recursively enumeates nested roles and logins
        Get-SqlDatabasePermission
            Returns a SMO DatabasePermission object with additional properites including the effective members of a
            grantee. Recursively enumerates nested roles and users
        Get-SqlObjectPermission
            Returns a SMO ObjectPermission object with additional properties including the effective members of a
            grantee. Recursively enumerates nested roles and users
        Get-SqlTable
            Returns a SMO Table object with additional properties
        Get-SqlStoredProcedure
            Returns a SMO StoredProcedure object with additional properties
        Get-SqlView
            Returns a SMO View object with additional properties
        Get-SqlUserDefinedDataType
            Returns a SMO UserDefinedDataType object with additional properites
        Get-SqlUserDefinedFunction
            Returns a SMO UserDefinedFunction object with additional properites
        Get-SqlSynonym
            Returns a SMO Synonym object with additional properites
        Get-SqlTrigger
            Returns a SMO Trigger object with additional properites. Note: A Trigger can have a Server, Database or Table/View parent object.
        Get-SqlColumn
            Returns a SMO Column object with additional properites. Note: A Column can have either a Table or View parent object.
        Get-SqlIndex
            Returns a SMO Index object with additional properites. Note: An Index can have either a Table or View parent object.
        Get-SqlStatistic
            Returns a SMO Statistic object with additional properites
        Get-SqlCheck
            Returns a SMO Check object with additional properites. Note: A Check can have either a Table or View parent object.
        Get-SqlForeignKey
            Returns a SMO ForeignKey object with additional properites
        Set-SqlScriptingOptions
            Sets scripting option used in Get-SqlScripter function by reading in the text file scriptopts.txt
        Get-SqlScripter
            Returns a SMO Scripter object. Any function which returns a SMO object can pipe to Get-SqlScripter. For example to script out all table
            in the pubs database: Get-SqlDatabase MyServer | Get-SqlTable | Get-SqlScripter
        Get-Information_Schema.Tables
            Returns the result set from INFORMATION_SCHEMA.Tables for the specified database(s) along with the Server name
        Get-Information_Schema.Columns
            Returns the result set from INFORMATION_SCHEMA.Columns for the specified database(s) along with the Server name
        Get-Information_Schema.Views
            Returns the result set from INFORMATION_SCHEMA.Views for the specified database(s) along with the Server name
        Get-Information_Schema.Routines
            Returns the result set from INFORMATION_SCHEMA.Routines for the specified database(s) along with the Server name
        Get-SysDatabases
            Returns the result set from sysdatases for the specified server along with the Server name
        Get-SqlDataFile
            Returns a SMO DataFile object with additional properties
        Get-SqlLogFile
            Returns a SMO LogFile object with additional properties
        Get-SqlVersion
            Returns a custom object with the Server name and version number
        Get-SqlPort
            Uses SQL-DMO to return the port number of the specified SQL Server
        ConvertTo-ExtendedPropertyXML
            Helper function returns XML representation of the Extended Properties of a SMO object
        Get-Sql
            Uses WMI to list all of the SQL Server related services running on the specified computer along with the service state and service account
        ConvertTo-StatisticColumnXML
            Helper function returns XML representation of the Statistic Columns of a SMO Statistic object
        ConvertTo-IndexedColumnXML
            Helper function returns XML representation of the Indexed Columns of a SMO Index object
    LibraryAgent.ps1 functions
        Get-AgentJobServer
            Returns a Microsoft.SqlServer.Management.Smo.Agent.JobServer Object. This is the top level object for Agent.Smo
        Get-AgentAlertCategory
            Returns an SMO.Agent AlertCategory object or collection of AlertCategory objects
        Get-AgentAlert
            Returns an SMO.Agent Alert object or collection of Alert objects
        Get-AgentJob
            Returns an SMO.Agent Job object or collection of Job objects
        Get-AgentJobSchedule
            Returns an SMO.Agent JobSchedule object or collection of JobSchedule objects for Job Objects
        Get-AgentJobStep
            Returns an SMO.Agent JobStep object or collection of JobStep objects
        Get-AgentOperator
            Returns an SMO.Agent Operator object or collection of Operator objects
        Get-AgentOperatorCategory
            Returns an SMO.Agent OperatorCategory object or collection of OperatorCategory objects
        Get-AgentProxyAccount
            Returns an SMO.Agent ProxyAccount object or collection of ProxyAccount objects
        Get-AgentSchedule
            Returns an SMO.Agent JobSchedule object or collection of JobSchedule objects for JobServer Shared Schedules
        Get-AgentTargetServerGroup
            Returns an SMO.Agent TargetServerGroup object or collection of TargetServerGroup objects
        Get-AgentTargetServer
            Returns an SMO.Agent TargetServer object or collection of TargetServer objects
        Set-AgentJobHistoryFilter
            Sets filtering option used in Get-AgentJobHistory function
        Get-AgentJobHistory
            Returns an DataTable of job history, filtering can be applied by using the Set-AgentJobHistoryFilter function
    LibraryRmo.ps1 functions
        Get-SqlConnection
            Returns a ServerConnection object
        Get-ReplServer
            Returns an RMO.ReplicationServer
        Get-ReplLightPublication
            Returns an RMO.LightPublication
        New-ReplTransPublication
            Constructor for RMO.TransPublication
        New-ReplMergePublication
            Constructor for RMO.MergePublication
        Get-ReplSubscriberSubscription
            Returns an RMO.SubscriberSubscription. Note: this is the only function executed on a subscriber
        Get-ReplPublication
            Returns either an RMO.TransPublication or RMO.MergePublication object
        Get-ReplSubscription
            Returns an RMO.TransSubscription or RMO.MergeSubscription object from a Publication
        Get-ReplArticle
            Returns an RMO.TransArticle or RMO.MergeArticle object from a Publication
        Get-ReplMonitor
            Returns an RMO.ReplicationMonitor
        Get-ReplPublisherMonitor
            Returns an RMO.PublisherMonitor
        Get-ReplPublicationMonitor
            Returns an RMO.PublicationMonitor
        Get-ReplEnumPublications
            Calls the EnumPublications method on a PublisherMonitor object
        Get-ReplEnumPublications2
            Calls the EnumPublications method on a PublisherMonitor object
        Get-ReplEnumSubscriptions
            Calls the EnumSubscriptions method on a PublicationMonitor object
        Get-ReplEnumSubscriptions2
            Calls the EnumSubscriptions2 method on a PublicationMonitor object
        Get-ReplTransPendingCommandInfo
            Calls the TransPendingCommandInfo method on a PublicationMonitor object
        Get-ReplEnumLogReaderAgent
            Calls the EnumLogReaderReader method on a PublicationMonitor object
        Get-ReplEnumSnapshotAgent
            Calls the EnumSnapshotAgent method on a PublicationMonitor object
        Set-ReplScriptOptions
            Sets the Enum ScriptOptions for scripting RMO objects. Unlike SMO which has a default script options
            RMO at at a minimum CREATION enum must be specified.
        Get-ReplScript
            Calls Script Method on RMO objects include ReplicationServer, Publication, Subscription and Articles
    LibrarySSIS.ps1 functions:
        Copy-ISItemSQLToSQL
            Copies a Package or SSIS folder from SQL to SQL
        Copy-ISItemSQLToFile
            Copies a Package or SSIS folder from SQL to file
        Copy-ISItemFileToSQL
            Copies a Package or SSIS folder from file to SQL
        Get-ISData
            Executes a query and returns an ADO.NET DataTable
        Get-ISItem
            Retrieves a list of SQL Server Integration Services folders and packages from the specified SQL Server instance. Returns a PackInfo Object.
            Note: Unlike the other SSIS functions this function requires a SQL instance name i.e. serverName\instanceName
        Get-ISPackage
            Retrieves an SSIS package from the specified Integration Services server or file path. Returns a Package Object
        Get-ISRunningPackage
            Returns a list of running packages on the specified Integration Services server. Returns a RunningPackage object or collection of objects
        Get-ISSqlConfigurationItem
            Executes a query to retrieve a configuration item
        New-ISApplication
            Base object for all other functions. Executes new-object ("Microsoft.SqlServer.Dts.Runtime.Application") 
        New-ISItem
            Creates a SQL storage folder for the specified Integration Services server
        Remove-ISItem
            Deletes a SQL storage folder or package on the specified Integration Services server
        Rename-ISItem
            Renames a SQL storage folder or package on the specified Integration Services server
        Set-ISConnectionString
            Sets the Connection string for an SSIS package. Useful for package configuration connection string which cannot be set dynamically at run 
            or deploy time
        Set-ISPackage
            Saves an SSIS package to an Integration Services server or file path as a dtsx file.
        Test-ISPath
            Test the existance of a SQL storage folder or package on the specified Integration Services server
    LibraryShowmbrs.ps1 functions
        Get-ShowMbrs
            Recursivley enumerates local Windows and AD groups similar to the NT Resource utility showmbrs.exe

Scripts
    Get-InvalidLogins.ps1
        Lists invalid AD/NT logins/groups which have been granted access to the
        specified SQL Server instance. Script calls the system stored procedure
        sp_validatelogins and validates the output by attempting to resolve the sid
        against AD. The second level of validation is done because sp_validatelogins
        incorrectly reports logins/groups which have been renamed in AD. SQL Server
        stores the AD sid so renamed accounts still have access to the instance.
        Renamed logins/groups are listed with the renamed value in the newAccount
        property.
    Get-SessionTimeStamp.ps1
        Creates a global session timestamp
    Run-SmoToCsvFile.ps1
        Runs Write-SmoToCsvFile.ps1 with the specified number of threads.
    Test-SqlConn.ps1
        Verifies Sql connectivity and writes successful connection to stdout and
        failed connections to stderr. Script is useful when combined with other
        scripts which would otherwise produce a terminating error on connectivity
    Write-SmoCsvToDb.ps1
        Load the SMO Csv file into the specified database
    Write-SmoToCsvFile.ps1
        Generates an a csv file for all SQL Server security settings
    scriptopts.txt
        Scripting options file for SMO
    replscriptopts.txt
        Scripting options file for RMO

SQL Server Reporting Services (2005) reports
    See the screenshots_sqlpsx.docx for sample output of reports

    SQLPSXChangedDatabasePermission.rdl
    SQLPSXChangedDatabaseRole.rdl
    SQLPSXChangedLogin.rdl
    SQLPSXChangedObjectPermission.rdl
    SQLPSXChangedServerPermission.rdl
    SQLPSXChangedServerRole.rdl
    SQLPSXChangedSqlUser.rdl
    SQLPSXChangedSqlUserOwnedObject.rdl
    SQLPSXDatabasePermission.rdl
    SQLPSXDatabaseRole.rdl
    SQLPSXLinkedServerLogin.rdl
    SQLPSXLogin.rdl
    SQLPSXLoginGrouped.rdl
    SQLPSXObjectPermission.rdl
    SQLPSXServerPermission.rdl
    SQLPSXServerRole.rdl
    SQLPSXSqlUser.rdl
    SQLPSXSqlUserGrouped.rdl
    SQLPSXSqlUserOwnedObject.rdl

Global Variables
    The following global session variables are used to cache information used across functions

    $__SQLPSXUserMember
    $__SQLPSXDatabaseRoleMember
    $__SQLPSXLoginMember
    $__SQLPSXGroupUser
    $__SQLPSXIsDomain
    $__SQLPSXInvalidLogin
    $__SQLPSXSessionTimeStamp

SQLPSX Database
    SQLPSX.AllObject.sql
        Single script for all database objects
    SQLPSX.Job.sql
        SQL Agent job to schedule running of Run-SmoToCsvFile.ps1 and importing into database using Write-SmoCsvToDb.ps1
