Deleted default database for all users - now locked out of server! 2

[webuser]

I have a SQL server installation with 3 logins defined, among them the 'sa' user. All 3 logins have a database by the name of 'Emps' as their default database. Unfortunately, the 'emps' database was removed, and now I can't login to the SQL Server. I get the error
'Cannot open user default database. Login failed'.
I have tried this from Query Analyzer and Enterprise Manager with the same result. How can I get back into my server? I have several databases on this server and I know the password for the logins, but I can't get in to the Server to make any changes, including adding new users, reattaching the database or anything else...
Reinstallation of SQL Server is not an option.

Thanks in advance...

 

[j9]

Try logging in to the machine as an NT administrator and then changing the sa default database back to master, like it should be.

 

[webuser]

When you say log in to the machine, how do you mean? I have an NT administrator login set up on the SQL Server, but it's default database is also the database that was deleted, and so I get the same error no matter which login I use...

 

[BobLoblaws]

You could try a new installation and then import all the databases from the old server.

 

[webuser]

Thanks, but I was trying to avoid the re-installation. This Server is off-site, hours away. A re-installation would require travel to the site. I have to believe that there is a way around this...

 

[j9]

I think you can log in to the machine that SQL Server resides on(log in as either a local administrator or a domain administrator) and then re-set the default db for sa and the others. If you use Terminal Services software or something similar, you can log in to the machine remotely and obtain a view as if you were sitting right at that computer. Otherwise, have someone at the site do it.

 

[BobLoblaws]

If you know all the proper SQL statements and parameters to use for editing a user's defaultn database, then I would suggest getting someone to create a quick application that can connect to the server via ADO or something and executing the SQL.
The connection is the easy part. It's the SQL that I imagine is tricky.

 

[webuser]

I don't think an application like the one you mention would work. After all, I would need to be able to log in within the code, something the Server is not letting me do...

 

[BobLoblaws]

But you could create an ADO connection to the server and specify the master database in the connection string. I don't think it would cause a problem. Then executing the SQL. I agree it isn't a great solution but it can be used as a last resort for you.

 

[webuser]

I see what your saying. I'll give it a try. Thanks!

 

[EdVassie]

Another idea...

Add the -T3608 flag to the SQL Server startup parameters, by using regedit, and restart SQL Server. You should be able to log on using a System Admin account.

If this works, create a new 'Emps' database, then remove the -T3608 flag and restart SQL Server. Do not make any further changes, as SQL Server is a bit 'delicate' when the -T3608 flag is active.

You should then be able to make whatever further changes you need. This posting is a personal opinion only, and may not reflect reality.