Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2006-12-05 : 09:15:02
|
| We are using SQL Server 7 (yes - still!)I recently added a new database to the server and the default database for a lot of the logins changed to that database, which wasn't really what I wanted. So I have two questions really. Firstly does anyone know why this happened apart from the new database being nearer to A in the alphabet and what I can/ should do to prevent recurrenceSecondly, and more importantly I think. I know I can change the default database back to the one I want using sp_defaultdb and the syslogins and sysusers tables. My question is - can I only do this using a cursor or is there a more elegant solution? (Am I finally going to have to learn the syntax for cursors?)Many thanks in advancesteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
pootle_flump
1064 Posts |
Posted - 2006-12-05 : 09:30:42
|
quote:
Originally posted by elwoos
My question is - can I only do this using a cursor or is there a more elegant solution? (Am I finally going to have to learn the syntax for cursors?)
Well - you can dump your results into a table variable and loop through that. This has no real advantage over a cursor other than saving space in your brain that would otherwise be occupied by terms like FORWARD_ONLY and FAST_FORWARD. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-12-05 : 12:55:07
|
| Nope, no cursors:declare @sql varchar(8000)set @sql=''select 'exec sp_defaultdb ' + quotename(name, '''') + ', ''myOtherDb''; ' from syslogins where dbname='myDb'exec(@sql)Unless you have several hundred logins this should work in one go. Otherwise you might have to run it twice, or print out @sql and execute part of it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 13:42:57
|
| I tend to do:select 'exec sp_defaultdb ' + quotename(name, '''') + ', ''myOtherDb''; ' from syslogins where dbname='myDb'and Execute the result - rather than letting it run "automated" because I like the opportunity to eye-ball it.For a recurring unattended task different rules apply!, but for a one-off I think it saferKristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-12-05 : 13:47:22
|
Duh - oh yeah.You missing a @sql = @sql + in there though?  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-12-05 : 14:58:03
|
 Need. More. Coffee. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-12-07 : 03:27:24
|
| Many thanks Guys, greatly apopreciated.Leaves a few more brain cells to be killed of by alcohol!steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-12-07 : 05:01:52
|
quote:
Originally posted by elwoos
Leaves a few more brain cells to be killed of by alcohol!
Amen to that! |
|
|
|
|
|
|
Curse this