How can I change my 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In case you can t login to SQL Server   sqlcmd    E -S InstanceName    d master   Reference  https   support microsoft com en-us kb 307864

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  unlike the sp defaultdb solution user and database names are provided without quotes  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  unlike the sp defaultdb solution user and database names are provided without quotes  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In case you can t login to SQL Server   sqlcmd    E -S InstanceName    d master   Reference  https   support microsoft com en-us kb 307864

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How can I change my default database in SQL Server without using MS SQL Server Management Studio?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to ssms

Examples related to default-database