0

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

2

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.

  • I checked and for that login,the default database is master not the dropped database. – user9516827 Apr 5 '19 at 21:52
  • Hmmm. Then it should be able to log in just fine. Perhaps your connection is specifying the dropped database. In the Connect to Server dialog in SSMS, use the Options button to see the advanced options. That has a place where you can specify the database to connect to. Maybe that is specifying the dropped database. – Doug Deden Apr 5 '19 at 21:55
  • I am able to connect to the SQL instance,but when i refresh the established connection,i get this error message.sorry i was not clear. – user9516827 Apr 5 '19 at 21:57
  • 1
    @user9516827 What do you mean by "refresh"? Management Studio is not a web browser. – Aaron Bertrand Apr 5 '19 at 22:13
  • @AaronBertrand In object explorer , once i connect to the sql instance i can refresh using the little refresh button ryt.That is why i am talking about.I connect to sql instance using the mentioned login and i am able to connect.But when i refresh the connection i get the error message mentioned in the post.Please see the screenshot. – user9516827 Apr 7 '19 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.