Resolving Microsoft SQL Server Error 4064 with PowerShell

Recently, a fellow IT Pro contacted me and stated they were unable to login to one of their SQL Servers using Windows Authentication. The following error was generated when attempting to login to SQL Server Management Studio (SSMS).

sql-error-4064-1a.jpg

Their exact words were "I think we have a permissions problem". Clicking on the "Show technical details" icon at the bottom of that error message showed the following information.

sql-error-4064-2a.jpg

You can work around this problem by clicking on the "Options >>" button:

sql-error-4064-3a.jpg

Then changing the "Connnect to database" to one that exists and that you have access to:

sql-error-4064-4a.jpg

In this scenario, what had happened is the users' default database had been removed from this server and migrated to a new SQL Server. While it's easy enough to change a users default database in SSMS, what if you have numerous users to change it for?

I'll be using the dbatools PowerShell module which can be installed from the PowerShell Gallery. The Install-Module function used in the following example is part of the PowerShellGet module which exists by default on PowerShell version 5.0 and higher.

1Install-Module -Name dbatools -Force

To be clear, the dbatools PowerShell module is being installed on a Windows 10 workstation and its commands will be run remotely against the specified instance of SQL Server.

First, the following command gets a list of all the databases on the specified SQL instance and stores them in a variable. Then, it queries the instance for a list of SQL logins where the user is set to a database that doesn't exist on that instance.

1$Databases = (Get-DbaDatabase -SqlInstance sql08).Name
2
3Get-DbaLogin -SqlInstance sql08 -ExcludeSystemLogin |
4Where-Object DefaultDatabase -notin $Databases |
5Select-Object -Property Name, LoginType, DefaultDatabase

sql-error-4064-5a.jpg

While this same command could be written as a one-liner as shown in the following example, the one-liner would be less efficient because it would query the SQL instance for a list of databases for every single user instead of querying them one time, storing them in a variable, and then using the contents of the variable for the comparison as shown in the previous example.

1Get-DbaLogin -SqlInstance sql08 -ExcludeSystemLogin |
2Where-Object DefaultDatabase -notin (Get-DbaDatabase -SqlInstance sql08).Name |
3Select-Object -Property Name, LoginType, DefaultDatabase

To resolve the login problem for all of these SQL Server logins, their default database needs to be changed to one that exists. I searched through numerous commands from different PowerShell modules and didn't find one to accomplish the task of setting a users default database except upon initial creation.

Instead of resorting to leveraging SQL Server Management Objects (SMO) directly with PowerShell, I decided to add this functionality to the Set-DbaLogin command that's part of the dbatools module. If I had a need for it, I'm sure others do as well.

1Get-DbaLogin -SqlInstance sql08 -ExcludeSystemLogin |
2Where-Object DefaultDatabase -notin $Databases |
3Set-DbaLogin -DefaultDatabase master

sql-error-4064-6a.jpg

One thing that I don't like about the Set-DbaLogin command is that it returns results by default. Most commands that I've used from other PowerShell modules including the default ones that set something usually don't return results unless a PassThru parameter is specified.

One workaround for this if you don't want results is to simply pipe the results to Out-Null. I know that using [void] or assigning it to the $null variable is faster, but Out-Null feels more like the PowerShell way to me.

1Get-DbaLogin -SqlInstance sql08 -ExcludeSystemLogin |
2Where-Object DefaultDatabase -notin $Databases |
3Set-DbaLogin -DefaultDatabase master |
4Out-Null

sql-error-4064-7a.jpg

Now there are no logins set to databases that don't exist on this instance of SQL Server.

1Get-DbaLogin -SqlInstance sql08 -ExcludeSystemLogin |
2Where-Object DefaultDatabase -notin $Databases

sql-error-4064-8a.jpg

I also ended up finding a minor bug (typo) in the New-DbaLogin command and fixed it along the way.

µ