Are your users experiencing problems trying to login to your SQL Server? Are they facing the below error?
Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)
In this post, I’ll discuss how SQL Server logins end up with invalid default databases, how to identify and resolve the issue, and how to prevent this from happening.
How does it happen?
It simple. When you create a login, you normally assign a default database. When that login logs into SQL Server next time, SQL Server authenticates the login, and directs the connection to the specified default database. If you drop that default database at a later date, the login still holds a reference to the default database, and tries to access it at login time. That’s when the user receives ‘login failed’ error.
Tip
If you are a non-admin user, and struggling with this issue, whilst waiting for your DBA to resolve this. you can get around it by specifying another default database in the connection dialogue box of your SQL Server Management Studio. Similarly, you can use the -d parameter of sqlcmd to specify a different default database name, like ‘master’.
How to identify and resolve this issue?
I am going to share a stored procedure named ‘dbo.FixDefaultDatabasesForLogins‘, from my personal DBA toolkit. When executed, this stored procedure identifies all the logins that have an invalid default database name, and changes the default database name to either ‘master’ or a specied database.
It accepts a single input parameter named @NewDefaultDB, which defaults to ‘master’, but you are free to specify a different default database name, as long its a valid database name.
Here’s how to run this stored procedure:
--To change the default database name to 'master'
--for all logins with invalid default databases
EXEC dbo.FixDefaultDatabasesForLogins
GO
--To change the default database name to 'AppDB1'
--for all logins with invalid default databases
EXEC dbo.FixDefaultDatabasesForLogins 'AppDB1'
GOAnd here’s the actual stored procedure code:
IF OBJECT_ID('dbo.FixDefaultDatabasesForLogins', 'P') IS NOT NULL
BEGIN
RAISERROR('Procedure dbo.FixDefaultDatabasesForLogins already exists. Dropping and recreating', 0, 1)
DROP PROC dbo.FixDefaultDatabasesForLogins
END
GO
CREATE PROC dbo.FixDefaultDatabasesForLogins
(
@NewDefaultDB sysname = 'master'
)
AS
BEGIN
--Created by Vyas Kondreddi
--https://dutyDBA.com
SET NOCOUNT ON
SET @NewDefaultDB = COALESCE(@NewDefaultDB, 'master')
IF DB_ID(@NewDefaultDB) IS NULL
BEGIN
RAISERROR('The new default database name ''%s'' is invalid', 11, 1, @NewDefaultDB)
RETURN (-1)
END
DECLARE @LoginID int, @LoginName sysname, @CurrDefaultDBName sysname, @CMD nvarchar(300)
DECLARE @LoginList table
(
LoginID int IDENTITY(1, 1) PRIMARY KEY,
LoginName sysname,
DefaultDBName sysname,
NewDefaultDB sysname NULL,
Command
AS ('ALTER LOGIN ' + QUOTENAME(LoginName) + ' WITH DEFAULT_DATABASE = ' + QUOTENAME(NewDefaultDB))
)
INSERT INTO @LoginList(LoginName, DefaultDBName, NewDefaultDB)
SELECT
name,
default_database_name,
@NewDefaultDB
FROM
sys.server_principals
WHERE
DB_ID(COALESCE(default_database_name, 'master')) IS NULL
SET @LoginID = SCOPE_IDENTITY()
IF @LoginID IS NULL
BEGIN
RAISERROR('No logins found with invalid default databases', 0, 1, @CMD)
RETURN(0)
END
WHILE @LoginID > 0
BEGIN
SELECT
@LoginName = LoginName,
@CurrDefaultDBName = DefaultDBName,
@CMD = Command
FROM
@LoginList
WHERE
LoginID = @LoginID
RAISERROR('Login: %s Changing default database From: %s To: %s', 0, 1, @LoginName, @CurrDefaultDBName, @NewDefaultDB) WITH NOWAIT
RAISERROR('Executing: %s', 0, 1, @CMD) WITH NOWAIT
EXEC (@CMD)
SET @LoginID -= 1
END
RETURN (0)
END
GO
IF OBJECT_ID('dbo.FixDefaultDatabasesForLogins', 'P') IS NOT NULL
BEGIN
RAISERROR('Procedure dbo.FixDefaultDatabasesForLogins has been created successfully', 0, 1)
END
GOWhen you execute the stored procedure it fixes the default database names where they are invalid. It shows you a list of all logins that have an invalid database name, and shows you the command its running, to fix the default database.
As you’ll see in the output, the stored prcedure executes ALTER LOGIN command using the WITH DEFAULT_DATABASE option to specify the new default database name. In older versions of SQL Server, the ALTER LOGIN command might not work, and you have to use the now deprecated sp_defaultdb stored procedure instead.
How to stop logins from ending up with invalid default database names?
Again, its simple. As a DBA, you should amend your processes related to dropping or decommissioning SQL Server databases. Before dropping a database, first check if any logins have this daabase listed as their default database. If you find any, update their default database to a valid database name before dropping the database. Here’s a simple query to run before dropping a database:
SELECT name
FROM sys.server_principals
WHERE default_database_name = 'DatabaseToBeDropped'
GOHope you find this post and the stored procedure handy in dealing with the invalid default database issue (SQL Server error 4064). Please leave a comment below to share your thoughts.
