Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (which I've done) and then running scripts etc.

However, after restarting in single-user mode (with only the SqlServer service running) I am unable to log-in using either my Windows account or sa credentials. I get the error:

Failed to connect to <servername>

Login failed for user 'sa'. Reason: Server is in single user mode. 
Only one administrator can connect at this time. (Microsoft SSQL Server, Error: 18461)

After restarting the server normally (ie. multi-user mode) all the logins work fine.

Can anyone explain what is going on and/or how to correct this?

share|improve this question

7 Answers 7

This might be because you are attempting to connect using SSMS. Inside SSMS, Object Explorer will open one connection to the database, while each subsequent query window will open another connection to the database.

What I have done in the past is open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

  1. Click New Query
  2. Type the following into the Server Name dialog box: ADMIN:ServerName\InstanceName
  3. From here, you can run any scripts needed against your database in Single User Mode.

This requires the SQL Server Browser service to be running.

The real sticking point in all of this is that Object Explorer will eat up that "single user".

Hope this helps,

Matt

share|improve this answer
    
Thanks v. much for answering. AFAIK I'm not connected to ANYTHING via Object Explorer. Its late and I've been doing this all afternoon - hometime. I'll give your method a go tomorrow and report back ;-) –  5arx Mar 24 '11 at 18:03

In situations where you cannot connect to SQL Server normally, try using DAC.

share|improve this answer

I would suggest that you start the SQL Service from the command line, and then using SQLCMD to connect to and run your scripts.

share|improve this answer

If you're a SysAdmin on SQL Server, have you thought about putting the databases (ok, so there might be quite a few) into restricted_user mode? However, the apps and other users won't have elevated permissions.

share|improve this answer

There's no need to start the server in single user mode to transfer the logins using sp_help_revlogin.

share|improve this answer
    
No really confused - no less an authority than the MS Knowledgebase article itself states this explicitly ...? –  5arx Mar 24 '11 at 23:54
2  
I see nothing in the MSKB article about single user mode. I've used sp_help_revlogin dozens of times and I've never put the system into single user mode. –  mrdenny Mar 24 '11 at 23:57
    
Sorry, my bad, it was actually part of @SQLRockstar 's answer (dba.stackexchange.com/questions/1880/…) to the bigger question I was asking. –  5arx Mar 25 '11 at 0:00

To migrate logins without problem, just do the following:

  • start Business Intelligence Development Studio
  • choose new project
  • choose SSIS project
  • drag and drop the task of "copy logins from server to server"
  • specify source and destination
  • excute

... and done.

share|improve this answer
    
Damn that's good! So ... where were you in March 2011 ...? ;-) –  5arx Jan 8 '13 at 23:31

I have had a similar experience with Single User mode. It is easy to think, "I started the single user mode - I should be the single user." But applications do not know that. The first ting I check is stopping any application services before starting the single user mode. Another way to stop other connections in single user mode is to deny connection to the login(s) that are connecting before you. Finding out who logged in? Turn on "Login Auditing -- audit both failed and successful logins" in the instance security properties. Start SQL in single user mode wait a bit - then stop it. Then check the SQL Error log with notepad from the log file location. Any login that is connecting or attempting to connect will show up in the SQL Error Log. When those logins are denied - they cannot get connected before you. Once you are finished with single user mode -- remember to re-enable the logins.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.