Sql-server – Cannot open database “dbname” requested by login.The login failed.Login failed for user ‘DOMAIN\Windowsusername’

loginssql serversql-server-2016users

I had a database which i dropped using the below command.

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

Now when i refresh the connection in SSMS i get the error message Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'.Microsoft SQL Server,Error 4060

I remember that the owner for that database was the same login "DOMAIN\Windowsusername" .
So i think now when i connect using the same user login ,it is trying to connect to the dropped database.
Since the database is dropped i cannot change the owner of the db.
How can i get rid of this error.

Any help will be appreciated.
enter image description here

Best Answer

That login probably has the dropped database listed as his/her default database. You just need to assign a different default database.

In SSMS, connect to the server, then go to the Security section, then the Logins section. Find the login in question, right-click, then choose Properties. On the General tab, you'll find a "Default database" field. It probably has the name of the dropped database. Select a database that is still valid, and use the OK button to save your change.

Related Question