April 10, 2002 at 3:49 pm
On our SQL Server 2000 system, user logins are having their default databases reset to whatever the first database is in the database list.
Any idea how to prevent this?
Thanks
Benny
April 10, 2002 at 4:51 pm
Reset in SQL itself, or in the QA picklist?
Andy
April 10, 2002 at 4:59 pm
In SQL Server itself. bjl
Reset in SQL itself, or in the QA picklist?
Andy
[/quote]
April 10, 2002 at 5:49 pm
Their default is defined in their login in SQL Server. When you say the first database, what exactly do you mean and when is it happening?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 10, 2002 at 7:03 pm
That's exactly the problem- their defaults are getting REDEFINED in their logins. The
default database becomes whichever is alphabetically first in the database list.
Thanks
bjl
April 15, 2002 at 9:07 am
I have the same problem and would like to figure a way to reset it automatically via a script if possible.
April 15, 2002 at 9:53 am
I havent been able to reproduce. Have you tried profiling to see if you can see when it happens and what is doing it?
Andy
April 15, 2002 at 10:03 am
Try a couple things:
Log in as UserX in QA, note the default db. Check EM and the properties for this login and note this.
Restart SQL and repeat.
Now change the default db to pubs.
Repeat
Is it changed?
Restart SQL.
Is it changed?
Pls post some answers.
Steve Jones
April 15, 2002 at 10:23 am
The problem I have is with the wrong default db. The cause of my problem is different I am sure. It is a result of the database copy wizard. We are migrating 7.0 to 2000 and copy a few databses at a time. When it copies the logins If the database exists it sets it a default. When it does not master is set as default. When we do copy the database with the user in it the deafult stays as master on the 2000 server. I want to figure a way to change this via script if possible.
April 15, 2002 at 10:53 am
You would need to write a script that can move the database defaults for the users. There is not a way for SQL to know this (unless you copy the dbs first and the logins second.)
Steve Jones
April 15, 2002 at 11:01 am
I know just wondered if there was a script out there already didn't want to reinvent the wheel
thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply