SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing the Default DB for a Login–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

Then, as a sysadmin, I right clicked to attach a database back.

2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

and all of a sudden I get this error:

2017-10-23 15_59_08-Microsoft SQL Server Management Studio

Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

2017-10-23 16_01_20-Connect to Database Engine

Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:

ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER

If you are on an older version, something like this will work:

exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

Once that was done, the GUI dialog worked. A quick and easy fix in this case.

SQLNewBlogger

As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

You could do the same thing. Show that you can recover from errors.


Filed under: Blog Tagged: security, sql server, SQLNewBlogger, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...