6

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?

Improve this question

7 Answers 7

Reset to default
10

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

Improve this answer
1
  • 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 ;-)
    – immutabl
    Mar 24, 2011 at 18:03
5

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

Improve this answer
3
  • No really confused - no less an authority than the MS Knowledgebase article itself states this explicitly ...?
    – immutabl
    Mar 24, 2011 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, 2011 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.
    – immutabl
    Mar 25, 2011 at 0:00
4

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

Improve this answer
3

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.

Improve this answer
2

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.

Improve this answer
2

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
  • execute

... and done.

Improve this answer
1
  • Damn that's good! So ... where were you in March 2011 ...? ;-)
    – immutabl
    Jan 8, 2013 at 23:31
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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