SQL Server sa password

Question

SQL Server sa password

SQListic

SSCertifiable

Points: 6904
More actions
August 22, 2012 at 11:14 am

#263212

Hi experts,
how can i login into sql server, where i dont have sa password, dont know who had acces to that server,who is using it(no details at all).
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you must have physical control of the server to regain control;

basically you end up starting the server in single user mode; in that mode, any local administrator gets promoted to a syslogin auto-magically; from there you can change the sa password, add yourself or other users as sysadmins, etc.

here's just a couple of many links on how to do it:

http://www.bradg.co.za/?p=51

http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-if-you-do-not-know-the-login-and-passwor.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQListic SSCertifiable Points: 6904 More actions · Answer 2

now my sa is locked out

tried to login using single user mode as mentioned here

http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/[/url]

i cant see startup parameters in advanced tab.

any suggestions ?

SQListic SSCertifiable Points: 6904 More actions · Answer 3

now i am in windows admin group restarted the service under singleusermode with -m; paramater

tried connecting with sqlcmd

c:\>sqlcmd -S pdba-cps

stil getting 18456 level 14 error

note:pdba-cps is servername

any suggestions plz help me

andersg98 SSCertifiable Points: 5180 More actions · Answer 4

Syntax for connecting with SQLCMD is C:\SQLCMD -S Servername\InstanceName

can you connect with management studio? If so, then add your account as sysadmin then restart the server normally, enjoy.

If you can only use SQLCMD then try:

USE [master]

GO

ALTER LOGIN [sa] WITH PASSWORD=N'newpassword'

GO

SQListic SSCertifiable Points: 6904 More actions · Answer 5

i tried connecting with management studio

i am getting login failed for user 'abc\xyz' 18456 error

i dont have login in sql server before and even no body does 🙂

plz suggest me. am i typing something wrong to connect to server using sqlcmd?

andersg98 SSCertifiable Points: 5180 More actions · Answer 6

When you restart it in single user mode is there still applications or users that are connecting to the server? if so, then stop them. single user mode only allows one connection and if it starts and the application takes the one connection then you will not be able to connect.

SQListic SSCertifiable Points: 6904 More actions · Answer 7

sorry for asking that basic question?

How can i control the users? dont know who are connecting.

jasona.work SSC Guru Points: 50118 More actions · Answer 8

Possibly the easiest way, if you've got physical access / control to the server, is going to be to unplug it from the network until you're done. Bear in mind, doing this will prevent you from being able to access the network as well, so make sure anything you need is either saved on the server, or on a flash drive (if possible,) or printed out on paper...

Jason

SQListic SSCertifiable Points: 6904 More actions · Answer 9

do we need to have builtin adminsitrators enabled on sql server side inorder to have windows local admins login as single user mode usinf -m; as startup parameter?

hrr1117 SSC Journeyman Points: 88 More actions · Answer 10

I met the same trouble with you, but i use Smartkey SQL Password Recovery to help reset sa password, I think it also helps you well.

Reset SQL 2005 password
retrive SQL login password

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

Connect to SQL Server When System Administrators Are Locked Out