Unknown sa password

Question

Unknown sa password

tosscrosby

Hall of Fame

Points: 3140
More actions
December 3, 2004 at 8:11 am

#84377

I've inherited a box from another of our organizations but nobody knows the sa password. As this is a production server, how do I retrieve or change the sa password without corrupting anything in the databases?

Terry

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 1

If SQL Server was installed with mixed authentication then you can login as domain administrator. Then change the sa password.

tosscrosby Hall of Fame Points: 3140 More actions · Answer 2

Unfortunately it was with SQL authentication. I'm tempted to try reinstalling SQL but I'm not sure of the ramifications.

Terry

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Change the registry LoginMode to 2 will set SQL Server to mixed authentication mode. If you are lucky, the login "BUILTIN\Administrators" is stil there and you can then login with either domain or local administrator account.

Can you run sp_helplogins and post result here? Don't rush to reinstall SQL Server.

tosscrosby Hall of Fame Points: 3140 More actions · Answer 4

I didn't think re-installing was the best option. Here are the results.

LoginName SID DefDBName DefLangName AUser ARemote

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

BUILTIN\Administrators 0x01020000000000052000000020020000 master us_english yes no

CCIHCC\sqlserv 0x0105000000000005150000002FD5EC6DB644E423235F636B8A0C0000 master us_english NO no

developer 0x225AFB07A863354DABC7C86BAA7794F6 WOODARD_LIVE us_english yes no

pwadm 0xAA2232BBB31FCF4BB9451588F19C808D master us_english yes no

pwlogin 0x943036F882BDC44F833777443156FAAD master us_english yes no

pwsys 0x5829719552C0C54698C11F7A11C9DB38 master us_english yes no

sa 0x01 master NULL yes no

(7 row(s) affected)

LoginName DBName UserName UserOrAlias

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

BUILTIN\Administrators WOODARD_LIVE Administrators User

developer CCI db_owner MemberOf

developer CCI developer User

developer WOODARD_LIVE db_owner MemberOf

developer WOODARD_LIVE developer User

pwadm plnt32 PW_ALL MemberOf

pwadm plnt32 pwadm User

pwlogin plnt32 PW_AUTHENICATION MemberOf

pwlogin plnt32 pwlogin User

pwsys plnt32 db_owner MemberOf

pwsys plnt32 dbo User

sa master db_owner MemberOf

sa master dbo User

sa model db_owner MemberOf

sa model dbo User

sa msdb db_owner MemberOf

sa msdb dbo User

sa Northwind db_owner MemberOf

sa Northwind dbo User

sa pubs db_owner MemberOf

sa pubs dbo User

sa tempdb db_owner MemberOf

sa tempdb dbo User

Terry

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

The BUILTIN\Administrators logins is there, so just change the registry , restart SQL Server service, login the server with either domain or local administrator account, registry the SQL Server with NT authentication, connect to it and change the 'sa' password.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 6

To access SQL Server, you need to use the registry key for SQL Server 2000 and SQL Server 7.0 that determines the authentication mode of SQL Server.

In SQL Server 7.0, the key is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \MSSQLServer\MSSQLServer\LoginMode
In SQL Server 2000, the key is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
MicrosoftSQLServer\<instance_name>\MSSQLServer\LoginMode

The value of LoginMode is 1 for Windows Authentication only, and 2 for Mixed Mode. After locking yourself out, you can change the value of LoginMode to 2, restart SQL Server, and log in as the system administrator (sa), provided you know the sa password.

Hope this helps.

Hope this helps...

Ford Fairlane
Rock and Roll Detective