187

I dropped a database from SQL Server, however it turns out that my login was set to use the dropped database as its default. I can connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to. However, whenever I try to do anything in object explorer, it tries to connect using my default database and fails.

Does anyone know how to set my default database without using object explorer?

Improve this question
0

13 Answers 13

Reset to default
234

What you can do is set your default database using the sp_defaultdb system stored procedure. Log in as you have done and then click the New Query button. After that simply run the sp_defaultdb command as follows:

Exec sp_defaultdb @loginame='login', @defdb='master'
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks indeed. Worked like a charm (and taught me about the 'Options' dialogue to specify db).
This question has raised a question on meta
Doesn't work so well if you're in an AD group and you don't want to change the default database for dozens of other developers.
@DOK: But I would assume that if your log in is broken in this way so are all the other developers. So fixing it for all of them would surely be a good thing?
@MartinBrown Dozens of developers, dozens of databases, developers (contractors) coming and going all the time, shifting among projects. Someone would have to administer all those logins and roles. For security, you want to limit access to those who need it. So, a developer could wait around for the admin to grant permission on another database. Meanwhile, when the auditors come around, they ding you for having stale permissions for folks who no longer need them. Sometimes it's better to use the simplest approach: add or remove the person to AD groups. There is no one-size-fits-all approach.
|
71

Alternative to sp_defaultdb (which will be removed in a future version of Microsoft SQL Server) could be ALTER LOGIN:

ALTER LOGIN [my_user_name] WITH DEFAULT_DATABASE = [new_default_database]

Note: user and database names are provided without quotes (unlike the sp_defaultdb solution). Brackets are needed if name had special chars (most common example will be domain user which is domain\username and won't work without brackets):

ALTER LOGIN me WITH DEFAULT_DATABASE = my_database

but

ALTER LOGIN [EVILCORP\j.smith28] WITH DEFAULT_DATABASE = [prod\v-45]
Improve this answer

6 Comments

Well this question is getting a bit old now. At the time I was using SQL 2005. Alter Login wasn't added until the 2008 version.
@MartinBrown Yes, I've been using sp_deafultdb for ages. I was surprised that there is a "new" way.
Note: single quotes not needed around fields my_user_name or database and may cause an error. Obvious to experienced hands but maybe not so to those doing this occasionally. Square brackets optional, if fields represent contiguous text.
I connect to the PROD database via a group, so I don't have an individual Login on the server, when I try to run the above I get "Cannot alter the login 'DOMAIN\myuser', because it does not exist or you do not have permission.". even worse I'm in multiple groups because each one gives me specific access to one of the many DBs on the server. suggestions?
To find what a login's default database is: select name, default_database_name from sys.server_principals;
|
54

To do it the GUI way, you need to go edit your login. One of its properties is the default database used for that login. You can find the list of logins under the Logins node under the Security node. Then select your login and right-click and pick Properties. Change the default database and your life will be better!

Note that someone with sysadmin privs needs to be able to login to do this or to run the query from the previous post.

Improve this answer

3 Comments

this works fine! just typed there "master" db instead "default".
The issue I was having was that I was the sysadmin and had deleted the database I had set to default and as such couldn't even get to this dialog.
@MartinBrown be that as it may, I hadn't dropped any tables and I was still led to your question, but felt more comfortable with the GUI solution.
50

Thanks to this post, I found an easier answer:

  1. Open Sql Server Management Studio

  2. Go to object Explorer -> Security -> Logins

  3. Right click on the login and select properties

  4. And in the properties window change the default database and click OK.

Improve this answer

6 Comments

Welcome to StackOverflow and thanks for providing an answer. You might want to take a look at the features of the Markdown editor, so you can format code blocks, lists, etc.: stackoverflow.com/editing-help. I've edited your answer to improve the formatting for you.
The whole point of this question is that what you have described does not work if you have deleted the default database for your own login account. In my particular case I was the only administrator in the system so could not use someone else's account to achieve the same effect either.
@Mattijs Hope you read this last line of the question "Does anyone know how to set my default database without using object explorer?". Thats is just what you have asked to do.
This mightn't have answered the OP's question, but it answered mine. It's a useful answer.
This is not possible at Azure Databases :/
|
16

If you don't have permissions to change your default DB you could manually select a different DB at the top of your queries...

USE [SomeOtherDb]
SELECT 'I am now using a different DB'

Will work as long as you have permission to the other DB

Improve this answer

Comments

9

Click on options on the connect to Server dialog and on the Connection Properties, you can choose the database to connect to on startup. Its better to leave it default which will make master as default. Otherwise you might inadvertently run sql on a wrong database after connecting to a database.

enter image description here

enter image description here

Improve this answer

2 Comments

From the question: " I can connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to. However, whenever I try to do anything in object explorer, it tries to connect using my default database and fails." Though they may have fixed it since then, this question is quite old.
This is very useful when you manage to remove your default database :) To proceed with screenshot's form, you can somply click on database textbox and write "master" into it. After login you can choose whatevet db you need.
8
  1. Click on Change Connection icon
  2. Click Options<<
  3. Select the db from Connect to database drop down
Improve this answer

1 Comment

For some reason this has worked for me in some instances and not in others.
7

I'll also prefer ALTER LOGIN Command as in accepted answer and described here

But for GUI lover

  1. Go to [SERVER INSTANCE] --> Security --> Logins --> [YOUR LOGIN]
  2. Right Click on [YOUR LOGIN]
  3. Update the Default Database Option at the bottom of the page

Tired of reading!!! just look at following

enter image description here

Improve this answer

1 Comment

This is the easiest place to check for the current default
2

In case you can't login to SQL Server:

sqlcmd –E -S InstanceName –d master

Reference: https://support.microsoft.com/en-us/kb/307864

Improve this answer

Comments

1

This may or may not exactly answer the question, but I ran into this issue (and question) when I had changed my account to have a new database I had created as my "default database". Then I deleted that database and wanted to test my creation script, from scratch. I logged off SSMS and was going to go back in, but was denied -- cannot log into default database was the error. D'oh!

What I did was, on the login dialog for SSMS, go to Options, Connection Properties, then type master on the "Connect to database" combobox. Click Connect. Got me in. From there you can run the command to:

ALTER LOGIN [DOMAIN\useracct] WITH DEFAULT_DATABASE=[master]
GO
Improve this answer

Comments

0

There is a little icon for change the connection, click on that and then go to Options and Select the db from Connect to database drop down

enter image description here

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
0

With the MSSQL queries below, you can change database on sqlcmd:

USE testdb
GO

Then, you can check the currently used database:

SELECT DB_NAME()
GO

testdb

Then, you can show all the existed databases:

SELECT name FROM master.sys.databases
GO

master
tempdb
model
msdb
testdb

In addition, if you don't specify a database on sqlcmd, "master" database is used by default.

Improve this answer

Comments

-1

If you use windows authentication, and you don't know a password to login as a user via username and password, you can do this: on the login-screen on SSMS click options at the bottom right, then go to the connection properties tab. Then you can type in manually the name of another database you have access to, over where it says , which will let you connect. Then follow the other advice for changing your default database

https://gyazo.com/c3d04c600311c08cb685bb668b569a67

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.