SA Disabled\Locked

Question

SA Disabled\Locked

VastSQL

SSC-Dedicated

Points: 36097
More actions
March 27, 2013 at 5:52 am

#394474

Hi Experts,
We have two situations here
1. ‘SA’ is locked and we don’t have any login with sysadmin previleage
2.SA is disabled and no other logins in that server except builtin\administrator.
Can anyone please help me to login to server with admin rights or enable\unlock SA
TIA

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

Add yourself as a admin on the windows host, start SQL in single user mode, then use SQLCMD or SSMS in query mode only to add in your windows account as a sysadmin

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 2

Plz go through the link given below...

http://dilipparakala.blogspot.com/2013/02/forgot-sa-password-and-none-of-user-is.html

Lowell SSC Guru Points: 323518 More actions · Answer 3

I found this recently:

if powershell is installed on the server, you can use that to add yourself (your domain login) as a sysadmin;

this example does the same thing by running sqlcmd under a different user context from a scheduled task; i'll try and find the powershell linky too:

the advantage is you don't have to stop and start the server, or bring it to single user mode or anything.

http://sev17.com/2011/10/22/gaining-sql-server-sysadmin-access/

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!

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

Thanks everyone for the reply..

Will try and let you all know..

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

I have admin previleage in that machine..

I stopped sql server and agent and started sql server using net start mssqlserver -m

when i tried to connect using SQLCMD -U domain\user -P password..it failed..what to do next??

Lowell SSC Guru Points: 323518 More actions · Answer 6

no username or password.

here's some detailed instructions:

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

from the machine itself,

sqlservr -m"SQLCMD"

then when connected, add yourself or a new admin:

CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];

EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';

GO

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!

Ant-Green SSC Guru Points: 113499 More actions · Answer 7

Ratheesh.K.Nair (3/27/2013)
I have admin previleage in that machine..
I stopped sql server and agent and started sql server using net start mssqlserver -m
when i tried to connect using SQLCMD -U domain\user -P password..it failed..what to do next??

You don't need to use -U -P you need to use trusted connection -T

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 8

VastSQL

SSC-Dedicated

Points: 36097

March 27, 2013 at 7:29 am

#1601146

Thanks a lot it worked