15 Basic PowerShell SQL Commands

SQL is a programming language created in RDBMS. It is used to manage the structured data. SQL queries update, or retrieve the data from the database. Furthermore, the SQLServer module manages the server products such as Azure SQL database, and Azure Synapse Analytics.

Quick Outline:

PowerShell SQL Commands

• Add-RoleMember
• Remove-RoleMember
• Add-SqlFirewallRule
• Remove-SqlFirewallRule
• Add-SqlLogin
• Remove-SqlLogin
• Get-SqlAgent
• Get-SqlCredential
• Get-SqlDatabase
• Get-SqlLogin
• Invoke-Sqlcmd
• Set-SqlCredential
• Start-SqlInstance
• Stop-SqlInstance
• Get-Command

Conclusion

PowerShell SQL Commands

The SQLServer module PowerShell commands manages the server. These commands help communicate with the database. An SQL server has two modules, one is SQLPS (No longer supported) and the other is SQLServer (Currently used). The SQLServer module helps interact with the SQL server via PowerShell. SQL commands are available to the computers with the SQLServer module installed.

1. Add-RoleMember

The Add-RoleMember command adds a specific member to a particular database role.

Example:

This example will add a user to a database using its name and it will also define the user role:

According to the above code:

• First, specify the Add-RoleMember cmdlet and specify the username to it using the -MemberName parameter.
• After that, provide the database using the -Database parameter.
• Lastly, specify the member role using the -RoleName parameter.

2. Remove-RoleMember

The Remove-RoleMember command removes the existing member from a specific role in a database.

Example:

This example will remove a specific member from a role of the database using its name:

To remove a member from the database role:

• First, place the Remove-RoleMember command and specify the member name using the -MemberName parameter.
• After that, specify the database name to the -Database parameter.
• Lastly, provide the role from which you want to remove the user to the -RoleName parameter.

3. Add-SqlFirewallRule

The Add-SqlFirewallRule command adds the firewall rule to authenticate the connections to a SQL Server instance.

Example:

This example will add a firewall rule on the specified computer:

To add a Windows firewall rule on the local computer:

• First, specify the Get-SqlInstance command and provide credentials to the -Credential parameter.
• Then, provide the computer name to the -MachineName parameter and pipe it to the Add-SqlFirewallRule command.
• Again, specify the user credentials to the -Credential flag.

4. Remove-SqlFirewallRule

The Remove-SqlFirewallRule command disables the firewall rule that authenticates the connections to a SQL Server instance.

Example:

This example will remove the firewall rule that stops connections to all the instances of an SQL Server :

Note: The above code’s explanation is the same as for the Add-SqlFirewallRule command’s example except for the Remove-SqlInstance command.

5. Add-SqlLogin

The Add-SqlLogin command creates a login object in the instance of an SQL Server.

Example:

This example will create a SqlLogin type:

To create a SqlLogin type:

• First, specify the Add-SqlLogin command and provide the server instance to the -ServerInstance parameter.
• After that, login name to the -LoginName parameter, login type to the -LoginType parameter, and database type to the -DefaultDatabase parameter.

6. Remove-SqlLogin

The Remove-SqlLogin command removes login objects from the instance of SQL Server. It can remove an individual and multiple instances of an SQL server.

Example:

This example will remove a login object by using its name:

To remove the login object by name:

• First, place the Get-SqlLogin command and provide the server instance to the -ServerInstance parameter.
• Then, use the -LoginName parameter and specify the login name.
• After that, pipe the whole command to the Remove-SqlLogin command.

7. Get-SqlAgent

The Get-SqlAgent command gets the SQL agent present in the target instance of an SQL server.

Example:

This example will display the SQL agent of a server instance:

To get the SQL agent of a server instance, first, use the Get-SqlAgent command and then specify the server instance using the -ServerInstance parameter.

8. Get-SqlCredential

The Get-SqlCredential command gets the SQL credentials of an object.

Example:

This example will display the credentials of the object:

To get the credentials of the object, first, provide the Get-SqlCredential command and specify the name of the credentials using the -Name parameter.

9. Get-SqlDatabase

The Get-SqlDatabase command gets the SQL database for each database that is present in the target instance of an SQL server.

Example:

This example will get SQL Server instances on a computer:

According to the above code:

• First, place the Get-SqlInstance command.
• Then, provide the credentials to the -Credential flag.
• Then, provide the computer name to the -MachineName parameter and pipe it to the Get-SqlDatabase command.
• Again, specify the SQL credentials to the -Credential parameter.

10. Get-SqlLogin

The Get-SqlLogin command returns the SQL login objects in an instance of an SQL server.

Example:

This example will display all login objects for the specified instance:

To get the login objects of the specified instance, first, use the Get-SqlLogin command and specify the server instance name using the -ServerInstance parameter.

11. Invoke-Sqlcmd

The Invoke-Sqlcmd command runs a script containing statements supported by SQL.

Example:

This example will connect to the named instance and execute a script:

According to the above code:

• First, use the Invoke-SqlCmd command, and specify your query to the -Query parameter.
• Then, specify the server instance name to the -ServerInstance parameter.

12. Set-SqlCredential

The Set-SqlCredential command sets the username and password properties for an SQL credentials object.

Example:

This example will set the identity of an SQL credential object:

In the above code:

• First, use the Set-SqlCredential command, then specify the path of the SQL instance to the -Path parameter.
• After that, provide the identity you want to configure using the -Identity flag.

13. Start-SqlInstance

The Start-SqlInstance command starts the specified instance of an SQL Server instance.

Example:

This example will initiate all the instances of an SQL server on the specified computer:

According to the above code:

• First, use the Get-SqlInstance command, and specify the SQL credentials to the -Credential parameter.
• After that, specify the computer name to the -MachineName parameter.
• Then pipe the whole command to the Start-SqlInstance command and specify the credentials to the -Credential parameter.
• Lastly, specify the -AcceptSelfSignedCertificate parameter to run the scripts that are not digitally signed.

14. Stop-SqlInstance

The Stop-SqlInstance command stops the specified instance of an SQL Server instance.

Example:

This example will stop the SQL server instances:

Note: The code for stopping the SQL instance is the same as for starting it except for the Stop-SqlInstance command.

15. Get-Command

The Get-Command gets commands from the specified modules. To get the commands stored in the SQLServer module, we need to use the Get-Command cmdlet.

Example:

This example will get the commands from the PowerShell SQLServer module:

To get the list of SQL server commands, first, use the Get-Command cmdlet and then specify the SQLServer module using the -Module parameter:

Conclusion

The SQLServer module comprises various commands for SQL server management. PowerShell SQLServer module commands help manage the SQL server. The most commonly used SQL commands include PowerShell SQL Commands Add-RoleMember, Remove-RoleMember, Add-SqlFirewallRule, or Remove-SqlFirewallRule.