Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server PowerShell Extensions (SQLPSX) Part 1

By Chad Miller,

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
  • Download SQL Server PowerShell Extensions (SQLPSX)
  • 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:

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 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:


$db.Tables | select name, RowCount, DataSpaceUsed | Sort-Object -property DataSpaceUsed -descending

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' AdventureWorks
Get-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' AdventureWorks
Get-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' pubs
Get-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' pubs
Get-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.

Total article views: 16649 | Views in the last 30 days: 25
 
Related Articles
FORUM

MDX Member properties

Return member properties

FORUM

database properties not accessing

database properties not accessing

ARTICLE

Industrial-strength database documentation using Extended Properties

To finish this short series on extended properties a look at documenting sets of database objects

FORUM

differ between attribute,member and member properties in dimension

differ between attribute,member and member properties in dimension

BLOG

SQL server could not start cannot find object or property (0×80092004)

SQL server could not start cannot find object or property (0×80092004) Recently I got a call from m...

Tags
powershell    
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones