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).

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.

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

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

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.

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.

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.

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.

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.

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

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

µ

2 Comments

  1. Manish

    Pls on prod server we are still not allowed to use 3rdparty powershell modules so how we get these scripts

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: