Month: March 2011

Windows PowerShell

PowerShell powers automating different tasks and Windows PowerShell is the Microsoft’s task automation framework, consisting of a command-line shell and associated scripting language built on top of, and integrated with the .NET Framework. PowerShell provides full access to COM and WMI, enabling administrators to perform administrative tasks on both local and remote Windows systems.

Following links will be useful when dealing with PowerShell
PowerShell Tutorials – Variables
http://www.powershellpro.com/powershell-tutorial-introduction/variables-arrays-hashes/
Invoking SQL and read files
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_24152261.html
Executing Sql Scripts
http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
Execute all SQL files in a Directory with Powershell
http://www.sqlfeatherandquill.com/2011/01/10/how-to-execute-all-sql-files-in-a-directory-with-powershell/
Why This SQL Server DBA is Learning Powershell
http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
Windows PowerShell -Replace (Search and Replace)
http://www.computerperformance.co.uk/powershell/powershell_replace.htm

Create a Proxy in SQL Server Management Studio

Microsoft SQL Server Agent proxy accounts define a security context in which a job step can run.

To create a proxy account
1.In Object Explorer, expand a server.
2.Expand SQL Server Agent.
3.Right-click Proxies and select New Proxy.
4.On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before you create a proxy if one is not already available. For more information about creating a credential, see How to: Create a Credential (SQL Server Management Studio) or CREATE CREDENTIAL (Transact-SQL).
5.Check the appropriate subsystem for this proxy.
6.On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.


Create a Credential in SQL Server Management Studio

A credential is required to connect from a outside resource to SQL Server. I needed to created a proxy to run an Integration Service and I wanted to create a credential for that.
MSDN says that a single credential can be mapped to multiple SQL Server logins and a SQL Server login can be mapped to only one credential. Only users with ALTER ANY CREDENTIAL permission can create or modify a credential.


1.In Object Explorer, expand Security, right-click Credentials, and then click New Credential.
2.In the New Credentials dialog box, in the Credential Name box, type a name for the credential.
3.In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account. But the identity can be an account of another type.
4.In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
5.Click OK.To create a credential