SQL Server PowerShell Extensions (SQLPSX) Part 1

Solving a SQL Server administration task with PowerShell scripts often requires the use of SQL Server Management Objects (SMO). For a DBA new to PowerShell scripting this presents a doubly step learn curve first you must learn the PowerShell language and then you must learn the SMO objects necessary to accomplish your task. There are many wonderful books and tutorials available for
learning PowerShell.
A good starting point is the Windows PowerShell site.
For learning SMO objects, the goal of the CodePlex project SQL Server PowerShell Extensions (SQLPSX)
is to provide intuitive PowerShell functions around SMO objects to make working with SMO
from PowerShell easier. This article is not intended to be an introduction to
PowerShell, but rather an introduction to SQLPSX. A basic understanding of
PowerShell is helpful, but not necessary to follow the examples.

Getting Started

Install SMO which is included with SQL Server Management Studio

Install PowerShell

Set your PowerShell execution policy to remotesigned. Launch PowerShell and type Set-ExecutionPolicy RemoteSigned Note: If you are running Vista, follow these instructions

Unblock the SQLPSX PowerShell scripts. Right click each .ps1 file and click Unblock
as shown below:

Launch PowerShell and source or dot the LibrarySmo.ps1 file using the
following command:
. .\LibrarySmo.ps1
The LibrarySmo.ps1 file contains a library a functions and sourcing a
library file simply loads the definitions of the functions but does not execute
the functions.

Note: That is dot space dot. You can verify that the library is sourced with the
following:
Get-Command *et-Sql* | Select Name
As you can see, you should have several new functions:

The readme.txt file included in the releases section of SQLPSX contains the documentation of each function included below for your reference. A brief description of each is below:

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 enumerates
nested roles and logins

Get-SqlServerPermission90*

Returns a SMO ServerPermission object with additional properties
including the effective members of a grantee. Recursively enumerates
nested roles and logins

Get-SqlDatabasePermission

Returns a SMO DatabasePermission object with additional properties
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

* = Helper functions called by other functions and not used directly. Unfortunately in PowerShell 1.0 there isn't a way to make these functions private

Additional properties have been added to the base SMO objects where
appropriate. One special property is the members property, whenever permissions,
role membership or user/login information is listed the members property shows
the effective members within the context of the object. For example, the members
property of Get-SqlUser shows all of the members of a Windows group including
nested groups and the members property of Get-SqlDatabaseRole lists every member
of the role both direct and indirect i.e. enumerates Windows groups and other
Database Roles. Likewise for permission type functions (Get-SqlDatabasePermission,
Get-SqlObjectPermission, and Get-SqlServerPermission) the members property shows
the effective grantee of the assigned permission. The current list of extended
properties include:

members

Stores an array of the effective members of a user, role,
grantee/permission or login

Xmlmembers

Same as members but in XML format

timestamp

A session timestamp

objects

Database objects (schemas, tables, views, etc.) owned by a User

Server

SQL instance name

dbname

Database name

LinkedServer

Linked server name

DataSource

Linked server data Source property

Using SQLPSX

Get-SqlServer

Now that we have our functions sourced will look at a few examples. To
assign a SMO Server object to the variable $server From
the PowerShell prompt run
the command:$server = Get-SqlServer 'Z002\SqlExpress'
To see all of the methods and properties available for the $server variable pipe
the variable to the PowerShell built-in cmdlet Get-Member as shown here:
$server | Get-Member

Notice the EnumProcesses method, to call this method and see the
output formatted as a table, call the EnumProcesses() method of the $server
variable and pipe the output to the Format-Table cmdlet:
$server.EnumProcesses() | Format-Table

To see information about the SQL Server:$server.Information
You can also drill into the Information property, for example to see the SQL Server version run
the command:$server.Information.VersionString

Get-SqlDatabase

Get-SqlDatabase has one required parameter, a SQL Server and an optional parameter of a database and returns either a single SMO Database object if a database is specified or a collection of SMO Database objects for all the databases on the SQL Server
instance if the SQL Server is specified without a database. To create and assign a SMO database object to the variable $db and then see the available properties run
the following command:$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorks$db | Get-Member -type Property

We can use the Tables property of our Database object which returns a collection SMO Table objects
to list the Name, RowCount and DataSpaceUsed properties for each table and sort the output by DataSpaceUsed in descending order:

Get-SqlData

Get-SqlData executes a query against the specified server and database returning an ADO.NET DataTable.
To select all the records from the authors table:
$dt = Get-SqlData 'Z002\SqlExpress' pubs 'SELECT * FROM dbo.authors'
Now that we have a ADO.NET DataTable object we can pipe the output through PowerShell's Where cmdlet
to retrieve specific rows with the au_lname column equal to 'White': $dt | where { $_.au_lname -eq 'White' }

Set-SqlData

Set-SqlData is very similar to Get-SqlData, and is used where you need
to execute a query with no results. The following is an example which imports a
csv file called "authors.csv" into the authors table in the pubs database:Set-SqlData 'Z002\SqlExpress' pubs "BULK INSERT pubs..authors FROM 'c:\authors.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

Get-SqlUser

Get-SqlUser has a required parameter a SMO Database object. The
following is an example which first creates and assigns a SMO Database object to the variable $db and passes the variable as a parameter to Get-SqlUser:
$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorksGet-SqlUser $db

Get-SqlDatabaseRole

Get-SqlDatabaseRole has a required parameter of a SMO Database object.
As you can see the pattern for calling Get-SqlDatabaseRole is identical to
Get-SqlUser:
$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorksGet-SqlDatabaseRole $db

Get-SqlLogin

Get-SqlLogin has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.
The following is an example
Get-SqlLogin 'Z002\SqlExpress'

Get-SqlLinkedServerLogin

Get-SqlLinkedServerLogin has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.
The following example is an example:Get-SqlLinkedServerLogin 'Z002\SqlExpress'

Get-SqlServerRole

Get-SqlServer Role has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.
The following is an example
Get-SqlServerRole 'Z002\SqlExpress'

Get-SqlServerPermission

Get-SqlServerPermission has a required parameter of SQL Server. The
following is an example:Get-SqlServerPermission 'Z002\SqlExpress'

Get-SqlDatabasePermission

Get-SqlDatabasePermission has a required parameter of a SMO Database
object. The following is an example:$db = Get-SqlDatabase 'Z002\SqlExpress' pubsGet-SqlDatabasePermission $db

Get-SqlObjectPermission

Get-SqlDatabasePermission takes a required parameter of a SMO Database
object. The following is an example:$db = Get-SqlDatabase 'Z002\SqlExpress' pubsGet-SqlObjectPermission $db

Piping Get-SqlServer and Get-SqlDatabase

What if you want to know the version number of a list of SQL Servers? The
following is an example
which uses a text file containing a list of SQL Servers:Get-Content ./servers.txt | foreach { $srvr = Get-SqlServer $_ ; $srvr.Information | add-Member -memberType noteProperty -name Server -value $srvr.Name -passThru | Select Server, VersionString }
This example is little more complex than previous examples, what we've done is
get a list of SQL Servers from the text file servers.txt using the built-in PowerShell cmdlet Get-Content. Next we assign a SMO Server object to the variable $srvr. We then access the Server object Information property $srvr.Information and add a new property with the name
Server. And finally we select only the new Server and VersionString
properties.
The use of Get-Content to retrieve server names from a text file is good
is simple, but we're DBAs, we often store data in SQL Server tables not text files. Here's the same example as before only this time we use the SQLPSX function Get-SqlData to retrieve the server name from a table called SqlServer on
the Z002\SqlExpress server in the SQLPSX database:
Get-SqlData 'Z002\SqlExpress' SQLPSX "SELECT Server FROM dbo.SqlServer" | foreach { $srvr = Get-SqlServer $_.Server ; $srvr.Information | add-Member -memberType noteProperty -name Server -value $srvr.Name -passThru | Select Server, VersionString }
Since the Get-SqlDatabase function when called with only a SQL Server parameter returns a collection of Database objects you can pipe object to other cmdlets or functions to retrieve properties for all databases on the SQL Server:.
To see the database name and size of all databases on the specified SQL Server:
Get-SqlDatabase 'Z002\SqlExpress' | Select name, size
Or to retrieve the Users for each database on SQL Server:
Get-SqlDatabase 'Z002\SqlExpress' | Get-SqlUser
We can also do multiple piping operations, for example to retrieve the Users for each database
from a list of SQL Servers:
Get-Content ./servers.txt | foreach { Get-SqlDatabase $_ | Get-SqlUser }

Next Steps

SQLPSX is a community project hosted on CodePlex and we're looking for feedback
including your thoughts on new functions, scripts and cmdlets, so join the discussion and let us know
what you'd like to see in a future release.
The second article in this series will demonstrate importing
security information into a database and reporting against the information using
the SQL Server Reporting Services reports included in SQLPSX.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.