Unknown sa password
-
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 -
If SQL Server was installed with mixed authentication then you can login as domain administrator. Then change the sa password.
-
Unfortunately it was with SQL authentication. I'm tempted to try reinstalling SQL but I'm not sure of the ramifications.
Terry -
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.
-
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 -
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.
-
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
- In SQL Server 7.0, the key is:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply