Power. Shell in SQL Server . Depending on your job, you might find Power.

CodeGuru is where developers can come to share ideas, articles, questions, answers, tips, tricks, comments, downloads, and so much more related to programming in.

When you import the module, you'll probably get a warning about invalid verbs. All PowerShell commands, which are called cmdlets, are supposed to follow a standard.

Adding users to Active Directory. Preface: As you know, if you try to add AD users using lusrmgr.msc you will receive the following error: And since I cover creating. Scott Hanselman on Programming, The Web, Open Source.NET, The Cloud and More. How can I get a list of users from active directory? Is there a way to pull username, firstname, lastname? I saw a similar post where this was used: PrincipalContext.

Shell an invaluable tool. For most IT pros, it's not a matter of if you'll be using Power. Shell, only a matter of when. Related Articles: Using SQL Server Management Objects with Power. Shell. Why Power.

Shell for SQL Server? I want to introduce you to the world of Power. Shell from a SQL Server perspective. Naturally, I can't teach you everything you need to know about Power. Shell in a few thousand words, but I can demonstrate how Power. Shell and SQL Server can play together and perhaps that will be enough to get the ball rolling. If you're currently managing SQL Server with T- SQL, there's no reason to change.

But many SQL Server pros are finding ways to incorporate Power. Shell into their daily work. For me, the real strength is being able to leverage Power. Shell and T- SQL. With the release of Windows 8 and Windows Server 2.

Microsoft introduced Power. Shell 3. 0, but I'm not really going to touch on any of its specific features. I'm going to use the Power. Shell bits that are included with SQL Server 2. Power. Shell snap- ins introduced in SQL Server 2.

Personally, I've never found much use for this. The Power. Shell session you get is referred to as a mini- shell. In earlier versions of SQL Server, it provided only a subset of Power. Shell's functionality, which frustrated many people because they were expecting a full Power.

Shell experience. The mini- shell in SQL Server 2. I'm already in SSMS, why do I need a Power. Shell session? I'm going to run all my commands on the server, but it would be just as easy to use Power. Shell remoting from my desktop. Before you import it, though, your execution policy must first be configured to run scripts. At a minimum, run this command in an elevated session: PS C: \> set- executionpolicy remotesigned - force You only have to set this policy once.

Note that when entering this command, you shouldn't type the PS C: \> portion, because it's not part of the command. Rather, it's the command prompt showing you the current directory in which you should be working.

When learning Power. Shell, it's helpful to include that information, which is why I'm showing it here and in the commands that follow. Free Email Signature Software Download. All Power. Shell commands, which are called cmdlets, are supposed to follow a standard verb- noun naming convention. The verb is from a list of standard . NET verbs such as Get, Set, and Remove. Having a standard naming convention makes it easier to find commands.

The sqlps module has a few cmdlets that don't follow this convention. All this means is that it can be a little trickier to discover one of these imported SQL commands. In Power. Shell 2.

I did a moment ago, before you can use any of its commands. But if you aren't sure, you can ask Power. Shell for help. For example, to find out what the Invoke- SQLCmd does, you can run the command: PS C: \> help invoke- sqlcmd As Figure 2 shows, this command returns information about what the Invoke- SQLCmd cmdlet will do and how to use it. Some cmdlets include a link to an online Help file, which will give you the most up- to- date information. Assuming there's an online link, you can run a command like this: PS C: \> Help get- eventlog - online Some online pages even have community- contributed content.

A provider typically provides a management interface to an underlying technology. For example, out of the box Power. Shell includes providers for interacting with the file system, registry, and certificate store. Some Power. Shell modules and snap- ins include additional providers such as Active Directory. Adobe Reader Korean Language Support Package Downloader more.

You can view the installed providers with the command: PS C: \> get- psprovider The sqlps module includes the SQLServer provider. To get information about the SQLServer provider, you can run the command: PS C: \> help sqlserver Figure 3 shows the results.

The bottom line is that you can use this provider to navigate a SQL Server instance like it was a file system and use Power. Shell's object approach to retrieve information or make some changes.

You use the Set- Location cmdlet to change the current directory and the Get- Child. Item cmdlet to list the files and directories contained in the current directory. If you're familiar with the Windows command shell (cmd. So, for example, if you want to change to the SQLServer drive and lists its contents, you'd run the commands: PS C: \> cd sqlserver: PS SQLSERVER: \> dir Similarly, you can access your SQL Server instance and navigate through its contents.

For example, if your SQL Server instance name is chi- db. PS C: \> cd sql\chi- db. PS SQLSERVER: \sql\chi- db. Figure 4 shows the results of both sets of commands. For example, as you can see in Figure 4, one of the folders is Logins.

To see a list of the login accounts, you can run the command: PS SQLSERVER: \sql\chi- db. The results are shown in Figure 5.

For example, to see more information about the two Globomantics login accounts, you can send or pipe (. The same holds true for the other commands that wrap.) As you can see in Figure 6, this one command lists all the login accounts from the Globomantics domain as well as when each login account was created, when it was last modified, and its default database—all formatted in an easy- to- read list.

With a little experience, you'll be able to understand it with no problem. The point I'd like you to take away is what I accomplished with a basic Power.

Shell expression. For example, if you're running Power. Shell with the Power. Shell Integrated Scripting Environment (ISE), you can check out the databases on the chi- db. PS SQLSERVER: \sql\chi- db. As you can see, the table lists the databases, sorted by size, and displays some other information about them.

As an example, I'll walk you through changing the number of allowed account retry attempts (i. Account. Retry. Attempts property) for Database Mail. First, you need to change to the Mail directory on the chi- db. PS SQLSERVER: \sql\chi- db. PS SQLSERVER: \sql\chi- db. In the contents, you'll find the Configuration.

Values object. By piping the results of the dir Configuration. Values command to the Get- Member cmdlet, you can get the names and descriptions of that object's properties and methods. The command looks like this: PS SQLSERVER: \sql\chi- db. Configuration. Values .

In this case, you'd select the Name and Value properties and use them in the command: PS SQLSERVER: \sql\chi- db. Configuration. Values . As you can see, the number of allowed account retry attempts is currently set to 1. To make this easier, you can save an object to a variable. To save the Configuration.

Values object to a variable named $config, you'd run the command: $config = dir Configuration. Values The $config variable is an array of configuration objects whose values you can get and set. Because it's a zero- based array and the Account. Retry. Attempts property is the first key in it, you can use $config. For example, the following command retrieves the Account. Retry. Attempts property's current value (i. PS SQLSERVER: \sql\chi- db.

As you can see, the number of allowed account retry attempts has been changed to 3. Thus, when possible, you'll want to take advantage of the sqlps cmdlets because they're easier to use. The cmdlet I find the most valuable is Invoke- SQLCmd. This cmdlet was first introduced in SQL Server 2. Help file for whichever version you're using.

It does so through its Query parameter. Essentially, any T- SQL expression can be executed from the Power. Shell prompt, like this: PS C: \> invoke- sqlcmd - query .

But you can specify a different machine and instance, like this: PS C: \Users\administrator> invoke- sqlcmd . But for now, let's run a few commands on the local server. Suppose you have a script, New- Computer. Data. DB. sql, that creates a database named Computer. Data. You can use the Invoke- SQLCmd cmdlet to run that script: PS C: \> invoke- sqlcmd- Input.