| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2006-03-17 : 08:57:35
|
| I've recently moved my transaction logs onto some new discs, pretty much following the instructions on the MS website here [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k[/url] though I also reindexed one of the tables while I was at it not that I think that is particularly relevant.When I reattached, all seemed well but for some reason, all the SQL (as opposed to the NT authenticated) users who had this d/b as their default couldn't get on. The error message was something like Couldn't access database ID 1 using master instead.Fortunately there aren't many of them so I reset their defaults back to this database by hand and they are fine now. I just thought that this was a bit odd. Superficially this is similar to the orphaned users issue but I didn't move the data files, nor did I move it to a different server nor a backup/restore. All I did was detach, copy the log files to the new location and then an attach, which to be honest went pretty smoothly apart from this.Has anyone come across this or perhaps know why this happened? Is it a bug?thankssteve-----------Oh, so they have internet on computers now! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-17 : 13:08:26
|
| Probably not relevant, but if you Rename a database any user with that as a default will get the new-named database instead, EVEN IF you create a new database with the original name.If you OFFLINE a database which is the Default for some users they will be unable to connect (or will switch to Master, I forget which).Sounds to me like there is an ID number being used behind the scenes for "Which DB is my default" and that is getting lost-in-the-wash for some of these type of operations.Kristen |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-17 : 13:48:27
|
| The problem is that when you re-attached the database, SQL Server assigned a new dbid in master.dbo.sysdatabases. This was different than the old dbid. The default database setting uses dbid and not the name of the database, hence your problem.Tara Kizeraka tduggan |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-20 : 08:12:41
|
| Thanks both, it suddenly all makes a lot more sense. I've also realised (as implied by your answers) that this issue does still exist with the Windows Authenticated users, it's just that the ODBC settings (which is how most users connect)set a default database to the correct onesteve-----------Oh, so they have internet on computers now! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-20 : 12:39:48
|
quote:
Originally posted by elwoos
Thanks both, it suddenly all makes a lot more sense. I've also realised (as implied by your answers) that this issue does still exist with the Windows Authenticated users, it's just that the ODBC settings (which is how most users connect)set a default database to the correct onesteve-----------Oh, so they have internet on computers now!
This issue is not for the default database inside the ODBC settings but rather it's for the default database inside SQL Server on the login.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-20 : 13:02:50
|
| Never thought of it that way, but clearly if you set a Default Database (or "Change database to ..." or whatever its called!!) in a DSN then that is going to be by Name, at runtime, and not by ID as-was-when-DB-was-created!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-20 : 13:16:53
|
| Yeah but the problem that Steve is mentioning has to do with when you connect to SQL Server it tries to put you in the default database specified in your login. Since that dbid doesn't exist, it barfs with that error. The default database in the ODBC DSN has to do with the connection string.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-20 : 13:43:09
|
| Indeedie, ta. I just never thought about it being that way before ... sorry!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-20 : 16:15:03
|
| These kind of issues are why I always set the default database to master.It's just too easy to forget about the default database when you move databases around.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-21 : 10:17:47
|
| We have a "sandpit" database we use for Default - developers often run scripts without changing away from the Default - and we'd wind up with tons of junk in MASTER!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-21 : 11:04:04
|
| You give developers DBO privileges in master?You're breaking my heart! It's like giving a handgun to a child.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-21 : 13:46:19
|
| Yeah, sorry about that. You're right, but we do .. well, not on Production servers, on Dev stuff they have DBO most places so that they can get on, and tell me afterwards what they did. They have good manners ... trained over many years ... so it works OK in practice.And heck, even I'm capable of running a script on the wrong database! So it's more "insurance" than "by design"!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-21 : 13:57:40
|
| I protect myself from myself by setting a default database ( -d ) on my Query Analyzer shortcuts.I have a menu with a Query Analyzer shortcut for each server I work with. When I click on one, it starts up without asking me about server or login and puts me into pubs or tempdb.I especially stay away from changing my own default database, because I don't want to be in the position of not being able to login because my default DB is not abailable.CODO ERGO SUM |
|
|
|
|
Strange happenings