Geeks With Blogs

News View David Douglass's profile on LinkedIn

My Presentations
David Douglass .NET on My Mind

What happens if you drop a user's default database in SQL Server 2005 and the user then tries to use SQL Server Management Studio?  You get this crystal clear error message:

image

What to do?  If you have another working administrative login, then you simply need to use that to update the properties of the user that can't connect:

image

But what if the user that can't connect is the only administrative user?  In that case, you need to tell SQL Server Management Studio to connect to a different database on startup, which is done using command line options:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S SQLSERVER2005 -d master -E

Here's all the command line options:

image

After executing the above command, SQL Server Management Studio will come up without the object explorer (the tree view on the left) open.  But, it will be connected to the master database and will allow you to run queries.  To fix the user login so that the object explorer can be opened, simply run a query to change the user's default database:

sp_defaultdb 'SQLSERVER2005\dbuser', master

You will be then be able to open the object explorer by using File -> Connect Object Explorer...

Appendix - exact message (for search engines):

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Posted on Saturday, January 19, 2008 8:00 AM | Back to top


Comments on this post: Dropping a User's Default Database in SQL Server 2005

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
You could also use SQLCMD.EXE to execute the sp_defaultdb stored procedure. The command-line parameters for SQLCMD would be the same (-S <server name> -D master -E).
Left by Joe Chung on Jan 20, 2008 7:51 AM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
Wish I had come across this before I reinstalled my SQL dev instance. Beauty of hindsight I guess.
Left by Johannes Prinz on Jun 22, 2008 10:18 PM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
You can also just click on "Options" button on the login dialog and type in master in the "connect to database" dropdown.
Left by Sean W. on Jul 10, 2008 10:37 AM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
I appreciate your posting on this problem which has helped me at the right time.

Great Regards
Murali
Left by V.Muralidharan on Jan 22, 2009 5:03 PM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
You can also rename a database so that it has the same name as the default database (just don't do it to a system database)
Left by David on Nov 05, 2009 4:55 PM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
Thanks for the straight forward solution. With my DBA on vacation You really helped me out.
Left by Jim Branson on Apr 15, 2010 11:52 AM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
Thanks Sean W. for the simplified and effective advice. I suggest, that if anyone is having this problem please try what Sean recommended. Thanks, again Sean.
Left by Alonso Muhammad on Jun 28, 2010 7:19 PM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
Thanks for your post! I used similar steps for my SQLEXPRESS version and it worked! If you are using SQL Server Management Studio, you can connect to another database at the time of login by selecting options. If you can't browse databases there, just type "master" and this should work.
Left by Abhay T on Jul 01, 2010 6:13 AM

# re: Dropping a User's Default Database in SQL Server 2005
Requesting Gravatar...
This worked great via SQLCMD. I never use it and so it took me a bit to remember I needed to enter GO after entering the stored proc command. Awesome! Saved me some major embarrassment!!!
Left by JDWolf on Mar 07, 2014 11:24 PM

Your comment:
 (will show your gravatar)


Copyright © David Douglass | Powered by: GeeksWithBlogs.net