How to change default database in SQL Server without using MS SQL Server Management Studio?

Question

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?

score 230 · Answer 1 · answered Dec 12 '08 at 10:38

230

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'

answered Dec 12 '08 at 10:38

Martin Brown

24,692
14
77
122

Thanks indeed. Worked like a charm (and taught me about the 'Options' dialogue to specify db). – penderi Sep 03 '10 at 13:24
This question has raised a question on [meta](http://meta.stackexchange.com/questions/74228/question-asked-and-answered-by-same-user-on-same-date-and-time) – abel Jan 07 '11 at 14:18
1

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 Nov 14 '11 at 20:01
@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? – Martin Brown Nov 15 '11 at 12:13
Well, there's a dozen different databases on this particular SQL Server instance. We would all want a different default database. If we created a different user group for each database so it could have the desired default database, then we would all have to be in a bunch of different user groups if we wanted to see different databases, and then we would lose the desired default. I wish there was a way in Management Studio to fix a default database, but so far I haven't found an approach that actually works. – DOK Nov 15 '11 at 14:35
@DOK: have you considered giving every developer there own login in SQL Server and then use SQL Server roles instead of AD groups? – Martin Brown Mar 14 '12 at 09:22
1

@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. – DOK Mar 14 '12 at 11:59

Marek · Accepted Answer · 2021-09-28T05:43:45.550

67

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]

edited Sep 28 '21 at 05:43

answered Nov 21 '17 at 13:20

Marek

49,472
15
99
121

1

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. – Martin Brown Nov 21 '17 at 15:19
@MartinBrown Yes, I've been using `sp_deafultdb` for ages. I was surprised that there is a "new" way. – Marek Nov 21 '17 at 16:49
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. – iokevins Jan 10 '19 at 19:26
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? – matao Feb 01 '19 at 02:58
1

To find what a login's default database is: select name, default_database_name from sys.server_principals; – DocOc Mar 18 '19 at 18:50
@matao You can accomplish this by using brackets around the user (and the database if necessary): ALTER LOGIN [DOMAIN\User] WITH DEFAULT_DATABASE = [SERVER\Database] – Spazmoose Feb 08 '20 at 01:43

score 53 · Answer 3 · answered Dec 12 '08 at 16:30

53

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.

answered Dec 12 '08 at 16:30

Marcus Erickson

1,561
1
11
10

this works fine! just typed there "master" db instead "default". – apprich May 04 '11 at 06:48
1

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. – Martin Brown Mar 12 '12 at 11:03
@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. – djv Jun 13 '14 at 17:40

score 47 · Answer 4 · edited Oct 14 '21 at 13:54

47

Thanks to this post, I found an easier answer:

Open Sql Server Management Studio
Go to object Explorer -> Security -> Logins
Right click on the login and select properties
And in the properties window change the default database and click OK.

edited Oct 14 '21 at 13:54

drac_o

427
5
11

answered Feb 19 '13 at 12:36

Mattijs

527
4
2

1

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.: http://stackoverflow.com/editing-help. I've edited your answer to improve the formatting for you. – Paul Turner Feb 19 '13 at 12:57
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. – Martin Brown Feb 19 '13 at 18:29
@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. – Rohit Vipin Mathews Aug 02 '13 at 10:07
2

This mightn't have answered the OP's question, but it answered mine. It's a useful answer. – CJ Dennis Dec 06 '16 at 23:31
2

This is not possible at Azure Databases :/ – Squazz Sep 28 '17 at 07:38

score 14 · Answer 5 · edited Jan 23 '16 at 08:39

14

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

edited Jan 23 '16 at 08:39

marc_s

732,580
175
1,330
1,459

answered Dec 12 '08 at 17:23

Greg B

14,597
18
87
141

score 8 · Answer 6 · answered Feb 23 '15 at 06:44

8

Click on Change Connection icon
Click Options<<
Select the db from Connect to database drop down

answered Feb 23 '15 at 06:44

Ravishankar S

335
4
5

For some reason this has worked for me in some instances and not in others. – levininja Mar 21 '17 at 16:27

score 7 · Answer 7 · answered Aug 24 '20 at 21:55

7

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.

answered Aug 24 '20 at 21:55

Chand

521
5
8

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. – Martin Brown Aug 26 '20 at 10:35

Mubashar · Answer 8 · 2019-07-17T06:51:58.133

6

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

But for GUI lover

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

Tired of reading!!! just look at following

edited Jul 17 '19 at 06:51

answered Feb 23 '19 at 13:51

Mubashar

12,300
11
66
95

This is the easiest place to check for the current default – DBADon Apr 28 '23 at 14:58

score 2 · Answer 9 · answered Nov 04 '16 at 13:24

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

answered Nov 04 '16 at 13:24

gmesorio

443
2
7
17

score 1 · Answer 10 · answered Aug 13 '18 at 21:03

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

score 0 · Answer 11 · answered Aug 18 '22 at 16:01

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

answered Aug 18 '22 at 16:01

Praganesh Patel

21
5

2

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](/help/how-to-answer). – Albert Logic Einstein Aug 23 '22 at 14:52

Super Kai - Kazuya Ito · Answer 12 · 2022-09-16T18:09:41.123

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.

edited Sep 16 '22 at 18:09

answered Sep 15 '22 at 19:56

Super Kai - Kazuya Ito

22,221
10
124
129

score -1 · Answer 13 · answered May 23 '17 at 10:53

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

How to change default database in SQL Server without using MS SQL Server Management Studio?

13 Answers13

Linked