CODEWITHSUNDEEP
sql-serverauthenticationoffline
GMasucci
GMasucci

Reputation: 2892

How can I regain access to MS SQL Server after default database for a user is taken offline?

Act in haste...

In the process of off-lining a database for backup I lost connection, the only user I had details for was set to use the database which I had just off-lined (thankfully not the master database) as the default database...problem as you can see.

In looking about on stack overflow I was unable to find a solution for my problem.

After a bit of digging about I remembered the command-line tool sqlcmd.

Upvotes: 5

Views: 3283

Answers (3)

Rm558
Rm558

Reputation: 5002

Thanks GMasucci, great answer. I am using windows account, here is what i did. 

sqlcmd -d master

then

alter login [domain\myName] with default_database = master
go
quit

Upvotes: 3

Peter Storms
Peter Storms

Reputation: 61

The other option is to click the "Options" button when logging into SSMS. There, you can specify which database to connect to. So as long as you have access to one of the other databases, such as master, you should be able to connect.

Upvotes: 6

GMasucci
GMasucci

Reputation: 2892

Repent at leisure:

Well the solution to the problem was pretty straightforward:

  • Open up a command-line ( Start->Run->cmd then hit Enter)
  • In the command-line type the following (substituting correct values where needed)
    • sqlcmd -S myserver\sqlexpress -d master -U myusername -P mypassword which starts a command-line connection to the database specified after -d
    • Note: ensure the switches are typed as above as unusually MS actually bothered with case sensitivity, so -s/-S, -d/-D, -u/-U and -p/-P are not interchangeable.
  • once the connection to the database is established the window will display a connection prompt similar to:logged in sqlcmd prompt
  • Once you have the correct prompt, type the following in:
    • alter login myusernamehere with default_database = master to set the default database for your user to master
    • On the next line type go and press enter . . . and that should be your user set to using the master database as its default database to connect to. It should appear on-screen to look like this: alter default database for user
  • Now you can connect with MS SQL Server Management Studio as normal and get the lovely graphical interface to use for your database admin tasks.
  • Carry out your management tasks and bring the database back on-line as you need/see fit
  • Set the default database for your user back to the database you have just brought back online, by changing the name of the database in the previous command to the name of the database you just brought back on-line, looking something like: restore default database for the user to the database it used to be

And that should be you set. I know this is an unlikely scenario for most, but should it happen to you, hopefully this will be of some use in restoring your normal access and getting the database back on-line.

Upvotes: 10

Related Questions