How to change user's default database using SQL for SQL Server 2000?

Question

I tried:

ALTER LOGIN  user
WITH DEFAULT_DATABASE = defaultDB

but it says:

Line 1: Incorrect syntax near 'LOGIN'.

I know this works in 2005+ but is there a different way in 2000?

RichardTheKiwi · Accepted Answer · 2011-02-01 01:23:17Z

14

exec sp_defaultdb @loginame='someone', @defdb='dbname'

Or since there are only those 2 parameters,

exec sp_defaultdb 'someone', 'dbname'

answered Feb 1, 2011 at 1:23

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edgar Froes Over a year ago

You are incredible, thanks. I was having problems with Navicat and DBComparer, they just couldn't connect to the database, because we removed the database that was related to the user we were using to log into the database server. Binding the user to an existing database fixed the issue.

RRUZ · Accepted Answer · 2011-02-01 01:49:28Z

0

Abe, the ALTER LOGIN statement was introduced in SQL Server 2005, you must try using the sp_defaultdb stored procedure how @cyberkiwi suggest.

answered Feb 1, 2011 at 1:49

RRUZ

137k20 gold badges365 silver badges489 bronze badges

Collectives™ on Stack Overflow

How to change user's default database using SQL for SQL Server 2000?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related