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?
This question is related to
sql-server
sql-server-2005
ssms
default-database
The answer is
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
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
- Click on Change Connection icon
- Click Options<<
- Select the db from Connect to database drop down
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.
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.
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
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'
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.
In case you can't login to SQL Server:
sqlcmd –E -S InstanceName –d master
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'
- Click on Change Connection icon
- Click Options<<
- Select the db from Connect to database drop down
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.
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
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.
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'
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
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.
In case you can't login to SQL Server:
sqlcmd –E -S InstanceName –d master
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.
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
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
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
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.
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
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
Source: Stackoverflow.com