Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

reset sa password Expand / Collapse
Author
Message
Posted Sunday, January 30, 2011 8:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
I have to reset the sa password . i am an admin on the box. i am not a sa . i dont know sa password. i tried to reset the password in single user mode . it doesnt work. Please help me
Post #1055893
Posted Sunday, January 30, 2011 10:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 42,751, Visits: 35,839
What do you mean 'it doesn't work'?
What exact steps did you follow? What errors did you get?

Do you have a sysadmin account other than sa? A windows authentication one?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1055904
Posted Sunday, January 30, 2011 10:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
I don't have any other sysadmin account, i am an administrator on the machine . i am not sysadmin. I don't have any other accounts with sysadmin priviliges.I am trying to do this on sql server 2008 server
Post #1055905
Posted Sunday, January 30, 2011 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 6,268, Visits: 13,501
start the sql server instance from the command prompt in single user mode. Open a second command prompt and connect to the instance using SQLCMD

sqlcmd -Sinstancename

Reset the password using

alter login [sa] with password = 'Newone1'

Exit both command prompts and restart the sql server services as normal


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1055914
Posted Sunday, January 30, 2011 6:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
i am getting the error user is in single user mode .only one administrator can connect at this time. error 18461.

when i checked the error log i get the following message

2011-01-30 19:56:42.77 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2K8R2 ].
2011-01-30 19:56:42.77 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2K8R2\sql\query ].
2011-01-30 19:56:42.79 Logon Error: 17187, Severity: 16, State: 1.
2011-01-30 19:56:42.79 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 2001:0:5ef5:73b8:28a7:2f3d:3f57:fefa]
2011-01-30 19:56:42.99 Logon Error: 17187, Severity: 16, State: 1.
2011-01-30 19:56:42.99 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.1.5]
2011-01-30 19:56:43.05 Server Server is listening on [ ::1 <ipv6> 6812].
2011-01-30 19:56:43.51 Server Server is listening on [ 127.0.0.1 <ipv4> 6812].

Thank You
Post #1055943
Posted Sunday, January 30, 2011 6:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
when i check the server properties . i can see allow remote connections checked in and remote query timeout 600


Post #1055945
Posted Sunday, January 30, 2011 6:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
Are you using SQLCmd or Management Studio? You don't want to use SSMS as it takes multiple connections






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1055946
Posted Sunday, January 30, 2011 7:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
I used both.First I tried sqlcmd and then I tried ssms
Post #1055950
Posted Sunday, January 30, 2011 10:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 42,751, Visits: 35,839
Make sure SQL Agent is stopped
Make sure any application that could connect is prevented from doing so
Make sure no one else is trying to connect



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1055966
Posted Sunday, January 30, 2011 10:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
sql server agent is off already. it is not running
Post #1055980
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse