≡ Menu

SQL Server PowerShell Extensions (SQLPSX)

SQL Server PowerShell Extensions (SQLPSX) is open source project hosted on CodePlex for purpose of providing an intuitive PowerShell scripting experience when working with SQL Server.  The original reason I created SQLPSX was to collect, analyze and report SQL Server security and permission settings and since then it has expanded to work with many of the more common SQL Server tasks. Work on SQLPSX started in September 2007 and the first Release 1.0 was completed in July 2008. Release 1.1 followed in September 2008 and includes the folllowing functions and reusable scripts:
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-SqlDatabaseRole — Returns a SMO DatabaseRole object with additional properties including the effective members of a role recursively enumerates nested roles, and users
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-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
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
Get-ShowMbrs — Recursivley enumerates local Windows and AD groups similar to the NT Resource utility showmbrs.exe
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.
Test-SqlConn.ps1 — Verifies Sql connectivity and writes successful connection to stdout and faiiled connections to stderr. Script is useful when combined with other scripts which would otherwise produce a terminating error on connectivity

Comments on this entry are closed.

%d bloggers like this: