Try to get in to SQL Server instance in single user mode through command prompt

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Try to get in to SQL Server instance in...

Try to get in to SQL Server instance in single user mode through command prompt

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Thursday, February 18, 2010 5:08 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi,

I'm Trying to get in to SQL Server instance in single user mode through command prompt to enable the SA account that i have disabled by mistake.

i Have stoped the SQL Server service and the SQL Server Agent service and then i have typed the folowing command on the CMD:

sqlservr.exe -m

The following messages appeared:

C:\Programas\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -m
2010-02-18 11:35:03.42 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Int
el X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2010-02-18 11:35:03.42 Server Error: 17054, Severity: 16, State: 1.
2010-02-18 11:35:03.42 Server The current event was not reported to the Win
dows Events log. Operating system error = 1502(O ficheiro de registo de eventos
está cheio.). You may need to clear the Windows Events log if it is full.
2010-02-18 11:35:03.42 Server (c) 2005 Microsoft Corporation.
2010-02-18 11:35:03.42 Server All rights reserved.
2010-02-18 11:35:03.42 Server Server process ID is 3012.
2010-02-18 11:35:03.42 Server Authentication mode is MIXED.
2010-02-18 11:35:03.42 Server Logging SQL Server messages in file 'C:\Progr
amas\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2010-02-18 11:35:03.42 Server This instance of SQL Server last reported usi
ng a process ID of 3196 at 18-02-2010 11:34:46 (local) 18-02-2010 11:34:46 (UTC)
. This is an informational message only; no user action is required.
2010-02-18 11:35:03.42 Server Registry startup parameters:
2010-02-18 11:35:03.42 Server -d C:\Programas\Microsoft SQL Server\MS
SQL.1\MSSQL\DATA\master.mdf
2010-02-18 11:35:03.42 Server -e C:\Programas\Microsoft SQL Server\MS
SQL.1\MSSQL\LOG\ERRORLOG
2010-02-18 11:35:03.42 Server -l C:\Programas\Microsoft SQL Server\MS
SQL.1\MSSQL\DATA\mastlog.ldf
2010-02-18 11:35:03.42 Server Command Line Startup Parameters:
2010-02-18 11:35:03.42 Server -m
2010-02-18 11:35:03.45 Server SQL Server is starting at normal priority bas
e (=7). This is an informational message only. No user action is required.
2010-02-18 11:35:03.45 Server Detected 2 CPUs. This is an informational mes
sage; no user action is required.
2010-02-18 11:35:03.70 Server Using dynamic lock allocation. Initial alloc
ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor
mational message only. No user action is required.
2010-02-18 11:35:03.78 Server Attempting to initialize Microsoft Distribute
d Transaction Coordinator (MS DTC). This is an informational message only. No us
er action is required.
2010-02-18 11:35:03.83 Server The Microsoft Distributed Transaction Coordin
ator (MS DTC) service could not be contacted. If you would like distributed tra
nsaction functionality, please start this service.
2010-02-18 11:35:03.84 Server Database Mirroring Transport is disabled in t
he endpoint configuration.
2010-02-18 11:35:03.85 spid5s Warning ******************
2010-02-18 11:35:03.85 spid5s SQL Server started in single-user mode. This
an informational message only. No user action is required.
2010-02-18 11:35:03.85 spid5s Starting up database 'master'.
2010-02-18 11:35:03.91 spid5s Recovery is writing a checkpoint in database
'master' (1). This is an informational message only. No user action is required.

2010-02-18 11:35:03.96 spid5s SQL Trace ID 1 was started by login "sa".
2010-02-18 11:35:03.99 spid5s Starting up database 'mssqlsystemresource'.
2010-02-18 11:35:04.03 spid5s The resource database build version is 9.00.4
035. This is an informational message only. No user action is required.
2010-02-18 11:35:04.37 spid5s Server name is 'FSCG28'. This is an informati
onal message only. No user action is required.
2010-02-18 11:35:04.37 spid8s Starting up database 'model'.
2010-02-18 11:35:04.58 spid8s Clearing tempdb database.
2010-02-18 11:35:04.94 spid8s Starting up database 'tempdb'.
2010-02-18 11:35:05.11 Server A self-generated certificate was successfully
loaded for encryption.
2010-02-18 11:35:05.15 Server Server is listening on [ 'any' <ipv4> 1433].
2010-02-18 11:35:05.16 Server Server local connection provider is ready to
accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-02-18 11:35:05.16 Server Server named pipe provider is ready to accept
connection on [ \\.\pipe\sql\query ].
2010-02-18 11:35:05.17 Server Server is listening on [ 127.0.0.1 <ipv4> 143
4].
2010-02-18 11:35:05.18 Server Dedicated admin connection support was establ
ished for listening locally on port 1434.
2010-02-18 11:35:05.22 Server SQL Server is now ready for client connection
s. This is an informational message; no user action is required.
2010-02-18 11:35:05.35 spid8s Starting up database 'teste1'.
2010-02-18 11:35:05.35 spid11s Starting up database 'SGCT'.
2010-02-18 11:35:05.36 spid10s Starting up database 'msdb'.
2010-02-18 11:35:05.84 spid8s CHECKDB for database 'teste1' finished withou
t errors on 2010-02-03 17:31:36.363 (local time). This is an informational messa
ge only; no user action is required.
2010-02-18 11:35:06.34 spid5s Recovery is complete. This is an informationa
l message only. No user action is required.
2010-02-18 11:44:33.70 Logon Error: 18470, Severity: 14, State: 1.
2010-02-18 11:44:33.70 Logon Login failed for user 'sa'. Reason: The accou
nt is disabled. [CLIENT: <local machine>]
2010-02-18 11:45:03.94 spid1s A significant part of sql server process memo
ry has been paged out. This may result in a performance degradation. Duration: 0
seconds. Working set (KB): 824, committed (KB): 25292, memory utilization: 3%.
2010-02-18 11:50:38.87 spid1s A significant part of sql server process memo
ry has been paged out. This may result in a performance degradation. Duration: 3
31 seconds. Working set (KB): 2492, committed (KB): 22988, memory utilization: 1
0%.
2010-02-18 11:55:11.02 spid1s A significant part of sql server process memo
ry has been paged out. This may result in a performance degradation. Duration: 6
02 seconds. Working set (KB): 5088, committed (KB): 22988, memory utilization: 2
2%.
2010-02-18 12:00:37.19 spid1s A significant part of sql server process memo
ry has been paged out. This may result in a performance degradation. Duration: 9
28 seconds. Working set (KB): 5096, committed (KB): 22988, memory utilization: 2
2%.

It seems that SQL Server is started , but then i wnat to type the command "alter login 'sa' enable" and i can't because the cursor don't let me write anything.

Can someone help, please?

Thank you.

Post #867911

Sqlfrenzy

Posted Thursday, February 18, 2010 5:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:47 AM
Points: 1,431, Visits: 1,540

connect thru ssms or sqlcmd to run the command

Regards,
Sqlfrenzy

Post #867932

river1

Posted Thursday, February 18, 2010 6:00 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

How can i connect throught SSMS in single user mode?

Post #867953

MANU-J.

Posted Thursday, February 18, 2010 7:05 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:56 PM
Points: 1,658, Visits: 8,564

Note:
Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations. To manage SQL Server in single-user mode, execute Transact-SQL statements by connecting only through the Query Editor in Management Studio, or use the sqlcmd utility.

Mj

Post #868009

river1

Posted Thursday, February 18, 2010 7:25 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Thank you , my problem was solved with your help.

Post #868034

river1

Posted Thursday, February 18, 2010 8:49 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Your link resolved my problem, but i have dificulty in undestand something.

what are this users:

##MS_AgentSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##

Do they exist in all SQL Server 2005?

Thank you

Post #868104

MANU-J.

Posted Thursday, February 18, 2010 11:30 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:56 PM
Points: 1,658, Visits: 8,564

These are Certificate-based SQL Server Logins

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

##MS_SQLResourceSigningCertificate##

##MS_SQLReplicationSigningCertificate##

##MS_SQLAuthenticatorCertificate##

##MS_AgentSigningCertificate##

##MS_PolicyEventProcessingLogin##

##MS_PolicySigningCertificate##

##MS_PolicyTsqlExecutionLogin##

http://msdn.microsoft.com/en-us/library/ms181127.aspx

Glad I could help!
MJ

Post #868345

river1

Posted Friday, February 19, 2010 2:06 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Thank you very much for the tip

Post #868835

« Prev Topic | Next Topic »

Permissions