By: Aaron Nelson | Updated: 2017-02-13 | Comments (1) | Related: > PowerShell
Problem
Managing Logins on a single SQL Server isn't such a big deal, but when you need to administer multiple machines, it's great to be able to automate.
Common scenarios include, but are not limited to: needing to add a new SQL login or Windows group to a Prod, Dev, and QA instance, needing to verify that a particular login or group is in fact deployed to those instances; and needing to remove a login or group from instances like when someone leaves the company, or your organization has simply changed how the domain groups are organized and which ones your team needs to be using.
Solution
SSMS 2016 has brought us three new cmdlets for working with SQL Server Logins Add-SqlLogin, Remove-SqlLogin and Get-SqlLogin. These cmdlets are a step towards making development and deployment of environment changes a simpler, more consistent experience. While more cmdlets are likely to follow in the future (namely Set-SqlLogin), this tip aims to show you what you can already do today.
Get-SqlLogin
For starters, this cmdlet like many others in the SqlServer module can be called from within the SQLServer:\ Provider and when you have already navigated to a specific server\instance you are not required to supply a value for the -ServerInstance parameter, the cmdlet will detect your path and uses that.
CD SQLSERVER:\SQL\localhost\default\
Get-SqlLogin
In the image above only three properties are displayed for the logins that were found, Name, Login Type, and Created. There are quite a few more properties that you can retrieve if you ask for them, the problem is, knowing what you can ask for. To find out what other properties you can ask for, pick any login, use the Get-SqlLogin cmdlet to retrieve it, and then pipe the results to the Get-Member cmdlet, asking for only the properties.
Get-SqlLogin -LoginName sa | Get-Member -MemberType Properties
Alternatively, and in this case, my preferred method: You can ask for the same login, but instead of piping your results to the Get-Member cmdlet, pipe them to SELECT *, so that you can also see the value for those properties. As Rob is fond of saying, combining these two methods is something that you should always do when examining any object in PowerShell to understand it.
Get-SqlLogin -LoginName sa | SELECT *
The Get-SqlLogin cmdlet will show you all of the login types such as AsymmetricKey, Certificate, SQL Logins, Windows Users, Windows Groups, and more that currently exist on a SQL Server instance. You can filter that list down by picking one of those as an option for the -LoginType parameter.
Get-SqlLogin -LoginType
You can easily further filter down which logins are being returned by optional parameters such as -Disabled -Locked and -PasswordExpired. If the cmdlet cannot find any logins with the filters you have added, it will return an error telling you as such. This may be a little bit of a shock to SQL-People who are used to queries that find no rows to simply return "(0 row(s) affected )", so I wanted to call it out.
Get-SqlLogin -ServerInstance LocalHost -LoginType SqlLogin -Disabled -Locked -PasswordExpired
Leveraging Registered Servers/ Central Management Servers
As with many SQL PowerShell cmdlets, these cmdlets become significantly more useful when you have to repeat your task across multiple instances of SQL Server. Since the beginning, the SQL PowerShell Provider has made it easy to access your Registered Servers or Central Management Server lists and incorporate them in executing your cmdlets.
In the code below, we will recurse through all of our Registered Server and put them all into a new variable called "$RegisteredSQLs", we will use the "WHERE {$_.Mode -ne 'd'}" portion simply to strip away the names of any sub-folders you may have your Registered Servers organized into. After that, we will call the Get-SqlLogin cmdlet once for each instance of SQL Server that got loaded into our "$RegisteredSQLs" variable and tell it that we only want to see SQL logins.
This time however, instead of just asking for the default output, we are going to pipe the results of the Get-SqlLogin cmdlet over to the Select-Object cmdlet (but we will use the "SELECT" alias to that cmdlet because hey, we're SQL-people) and we will specify that we want it to return the Parent, Name, LoginType, CreateDate, and DefaultDatabase properties. I have also added piping the output to the Format-Table cmdlet, but that's just to ensure that it comes out in a nice tabular format, like data professionals are used to looking at.
I only have two instances of SQL Server in my Registered Servers, but that's enough to give you an idea of what the output of the code below can look like.
foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | WHERE {$_.Mode -ne 'd'} ) { Get-SqlLogin -ServerInstance $RegisteredSQLs.Name -LoginType SqlLogin | SELECT Parent, Name, LoginType, CreateDate, DefaultDatabase | Format-Table -AutoSize }
Please keep in mind that I could just have just as easily gone through a list of my Central Management Servers by simply swapping out 'Database Engine Server Group' for 'Central Management Server Group'. This same technique can be used for the Add-SqlLogin and Remove-SqlLogin cmdlets as well.
Add-SqlLogin
The Add-SqlLogin was the last of the three to be added to the SqlServer module and came out with SSMS 16.4 in September 2016. This cmdlet will allow you to add AsymmetricKey, Certificate, ExternalGroup, ExternalUser, SQL Logins, Windows Users, and Windows Groups to an instance of SQL Server. When adding these logins you can also set several optional parameters at the same time. The optional parameters will seem very familiar to anyone who has add logins to SQL Server before, -DefaultDatabase -EnforcePasswordPolicy -EnforcePasswordExpiration -MustChangePasswordAtNextLogin -Enable and -GrantConnectSql.
Add-SqlLogin -ServerInstance localhost\SQL2016 -LoginName SQLLoginToRemove -LoginType SqlLogin -GrantConnectSql -Enable
Note: By default logins added with the Add-SqlLogin cmdlet are disabled and denied permissions to connect to the database engine. This means that you will need to add the -GrantConnectSql and -Enable switch parameters if you want to be able to use the logins right away.
Pipelining
Pairing Get-SqlLogin with Add-SqlLogin is where you can really do some helpful stuff. Unfortunately, this cmdlet does not currently support direct pipeline input but it may in the future; in the meantime, it is possible to pipe logins into the Add-SqlLogin cmdlet if you wrap a foreach loop around it. In this scenario you will likely need to filter down the list of logins you are copying over, you can do this by specifying the name of the login you are want, the additional filtering parameters mentioned above, or by piping the results of the Get-SqlLogin cmdlet to the Out-GridView cmdlet and supplying the -PassThru parameter. Doing this will give you a grid of logins, pick as many as you need to add to the other instance and click the OK button. Once you click OK the pipeline will resume and only the logins you chose will be sent down to the Add- SqlLogin cmdlet.
Get-SqlLogin -ServerInstance localhost -LoginType WindowsUser | Out-GridView -PassThru | foreach{ Add-SqlLogin -ServerInstance localhost\SQL2016 -LoginType $_.LoginType -LoginName $_.Name -DefaultDatabase $_.DefaultDatabase -GrantConnectSql -Enable }
Using the approach above, you can copy the logins and groups from one instance to another. Windows Users and Groups will copy over just fine without intervention. For SQL Logins however, using this approach you will need supply the password of the SQL Login that you are creating on the new machine.
When adding a SQL Login, the Add-SqlLogin cmdlet does not offer a parameter to pass in the password (well, not the way a SQL person might expect), instead you can pass it a PSCredential object to the -LoginPSCredential parameter; otherwise, it pops up a credential box and asks you to enter the password. This may be problematic if you don't know the password, and you may need to rely on the -MustChangePasswordAtNextLogin parameter. If you do know the password and need to add the same login to multiple instance you can set a variable to the results of a Get-PSCredential call and then pass in that PSCredential object to the parameter, that way you don't have to type in the same password multiple times.
$MSSQLTipsPassword = Get-Credential -Credential MSSQLTips foreach ($RegisteredSQLs IN dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group\' | WHERE {$_.Mode -ne 'd'} ) { Add-SqlLogin -ServerInstance $RegisteredSQLs.Name -LoginType SqlLogin -LoginName MSSQLTips -LoginPSCredential $MSSQLTipsPassword -Enable -GrantConnectSql }
I will include a community option on how to copy logins from one instance to another at the end of this tip.
Remove-SqlLogin
Just like the other two cmdlets, the Remove-SqlLogin can work with Windows groups, not just Windows and SQL logins. The Remove-SqlLogin allows you to specify multiple logins to remove via the -LoginName parameter or you can first use the Get-SqlLogin cmdlet to find the logins you want to remove, and then pipe the results into Remove-SqlLogin. By default, Remove-SqlLogin will prompt you to confirm each login you tell it to remove. You can override this behavior by supplying the -Force parameter which will instruct the cmdlet to remove the login without further prompting.
Remove-SqlLogin -ServerInstance localhost\SQL2016 -LoginName SQLLoginToRemove
Remove-SqlLogin -ServerInstance localhost\SQL2016 -LoginName SQLLoginToRemove -Force
-RemoveAssociatedUsers
The Remove-SqlLogin cmdlet also comes with a -RemoveAssociatedUsers parameter which will also remove any database users associated to the login being removed, from all databases on the instance. One caveat to this is that the user cannot own any objects in the database, otherwise removing the user will not succeed.
Remove-SqlLogin -ServerInstance localhost\SQL2016 -LoginName SQLLoginToRemove1, SQLLoginToRemove2, SQLLoginToRemove3 -RemoveAssociatedUsers
Summary
These three SQL PowerShell cmdlets will come in handy for many people. Especially in Development scenarios, these cmdlets can contribute to having a much more reliable build & deployment process.
If you interested in being able to do more than what these cmdlets can currently do and are able to use open source code in your environment, be sure to check out the Copy-SqlLogin command from the dbatools project.
Next Steps
- If you haven't done so already, download the latest version of SSMS 2016 here: https://msdn.microsoft.com/en-us/library/mt238290.aspx
- Watch this video from the PowerShell Virtual Group of PASS on using the new SQL PowerShell cmdlets https://youtu.be/sFKjE7Hsz38
- Check out my other tip on Using the New -OutputAs parameter for Invoke-SqlCmd
About the author
Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-02-13
