How to log in to sql server ??[If Sa disbaled ]

Question

How to log in to sql server ??[If Sa disbaled ]

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
August 31, 2012 at 12:14 am

#280912

Hi Team,
i am unable to log in to sql server, i forget the sa password and my administrator privileges log in is not working .
Could you please suggest me how to log in sql server ??
Thanks
Lavanya

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sumit Rastogi SSC Eights! Points: 838 More actions · Answer 1

Log on with Windows Authentication Privelages and change the password for sa, perhaps take care of applications with same sa paswords (incase).

Thanks,

Sumit Rastogi

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Is your Windows account a member of the Administrators group, and is the Administrators group a valid server login group with sysadmin permissions?

If so then log in using your regular Windows account.

Is sa the only login with sysadmin credentials on your system?

If you're using 2005, you can let yourself in through the kitchen window by adding yourself to the group(s) in which the SQL service account(s) reside, dependent on your configuration.

Alternatively if you're using a third-party product that requires sysadmin credentials (like the Red Gate Toolbelt suite) AND you've configured it to use a specific Windows group, login or SQL login, then try logging in using those credentials and changing sa from there.

If the DB is in single-user mode or there's another non-login related problem then try using the DAC (SQLCMD -SyourServer -E -A) or use admin:yourServer in SSMS with trace flags).

Finally, as a last resort, get hold of a decent debugger (freeware) and hook it up to the SQL service. Pre-2012, you should be able to lift the sa password from the memory associated with the service, by searching for the 'sa' Unicode string. WARNING: Do not do this on production as it will effectively freeze the service. Dependent on a) when you last restarted the service b) memory contention and page life expectancy on the server c) SQL Server version d) whether sa has been used to login recently.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

db_expert_pradeep SSC Veteran Points: 294 More actions · Answer 3

If your SQL is running with service account or any service account which have SYSADMIN privilages you can enter with that account and reset then SA password.

Divine Flame SSCoach Points: 15943 More actions · Answer 4

Lavanyasri (8/31/2012)
Hi Team,
i am unable to log in to sql server, i forget the sa password and my administrator privileges log in is not working .
Could you please suggest me how to log in sql server ??
Thanks
Lavanya

As per the title of the question "sa" is disabled & according to the question you have forgot the password of "sa". Also, you have not provided the status of "BUILTIN\ADMINISTRATOR" login on your server.

If you have not removed "BUILTIN\ADMINISTRATOR" from the logins (for the security purpose) & it is still present under SQL Server logins you can easily login using windows authentication. At the same time, if you had added any windows login as sysadmin earlier you can use that also.

Note: If you had removed the "BUILTIN\ADMINISTRATOR" login earlier & you don't have any other windows login which is configured as sysadmin in SQL Server then:

1. Start the SQL Server service in single user mode

2. Login to the system with any windows login which has administrator rights on the system

3. Login to the SQL Server using windows authentication

Sujeet Singh