Next, stop the SQL instance and then start it again without the '/m' switch so it is no longer in Single-User Mode. SP_ADDSRVROLEMEMBER RecoveryAcct,’SYSADMIN’ GOħ. Again, you'll need to use the 'GO' command and if you don't get an error, you can assume all is well. Now, use more T-SQL commands to add the user to the SysAdmin role. If you don't get an error, you can assume all is well.ĬREATE LOGIN RecoveryAcct WITH PASSWORD=’TempPass!’ GOĦ. There's no indication the command was successful you just end up back at a '1>' prompt. Since you're issuing T-SQL commands directly, you'll need to use the 'GO' command, too.
Microsoft sql server sa account password#
I'm going to create a login called "RecoveryAcct" and give it the password "TempPass!".
From here, you use Transact-SQL (T-SQL) commands to create a login. If you want to specify a particular SQL instance, just use the appropriate SQLCMD switches. This means you're connected to the default instance. You'll find yourself at a numbered prompt.
There's no indication you're connected in Single-User Mode, so don't worry if you don't see anything.Ĥ. The input following the '/m' switch is case-sensitive. The default is MSSQLSERVER.ģ. Start the SQL Instance using the '/m' switch and specifying you'll use SQLCMD to interact with the instance. You'll connect and interact with the SQL instance from a command prompt using SQLCMD commands.Ģ. While in Single User Mode, only one user can be connected at a time (as the name would imply). To launch in Single-User Mode, one must use a startup parameter for the SQL instance in question. In this mode, any account that is a member of the local Administrators group will be able to log in to the server with sysadmin privileges. Microsoft SQL Server has the ability to launch in Single-User Mode. Fortunately, there's a way to fix this problem without reinstalling SQL and reattaching the databases. Consequently, there was no way to log into the SQL server except for the sa account, and that password was long-forgotten. Recently, I ran into a scenario where a corrupt Active Directory had been rebuilt, however the SQL server logins got lost in the shuffle. Normally, it's not that big of a deal - just log in with a different account, right? Oh, wait. Someone installs a SQL server and then promptly forgets the sa password without documenting it.
0 kommentar(er)
