Advantages of using PowerShell
PowerShell scripts give DBAs more advantages than using standard T-SQL and SSIS for automation. These advantages include:
1. It is easier to automate multi-Server processing. We can execute any specific task on multiple SQL Server instances.
2. Easier access to Windows resources, such as files and folders, Windows Services, and printers.
3. Compared with SSIS, data loading is faster and easier.
4. It is easier to obtain the attributes of objects and processes on the server.
Function calls and scripts in SQLPSX
PowerShell scripts in SQLPSX can execute more than 100 SQL Server management tasks. Many function calls and scripts are concentrated on security settings, including logins users roles and permissions. Each function is described in Readme. Txt of the sqlspx project.
Common functions of SQL Server tasks:
Get-SqlServer: Call the Microsoft. SqlServer. Management. SMO. Server Object to obtain a list of all available SQL Server systems.
Get-SQLUser, which can Get the SMO user object. This object has additional attributes and lists all objects owned by this user.
Get-SQLData to obtain the SQL Server result set.
Get-SQLDatabase to obtain the attributes of one or more databases.
Get-SQLUser: obtains information about one or more users, including all objects owned by this user.
In addition, we can store the output of running scripts in the database, view the data through the SQL Server Report Service reports and queries, or through BID (Business Intelligence Development Studio) or Visual Studio to analyze security information.
SQLPSX System Requirements
Install SSMS of SQL Server2005 and later versions. Note: To set the PowerShell execution policy to remotesigned, you may need to unlock the SQLPSX PowerShell script so that they can run without limit.
Related Resources
Http://www.codeplex.com/SQLPSX
Author Blog: http://chadwickmiller.spaces.live.com