Regresar
-
Título
How to elevate a SQL Server login to the SysAdmins role and reset the SA password when no credential -
Descripción
When preparing SQL servers for AppAssure protection it is not uncommon to have a situation where the customer does not know the credentials for the a sysadmin or sa logins (or the user in the sysadmin role has been removed from Active directory) and thus cannot administer the SQL server. Under the circumstances, in order to avoid re-installing the SQL server, a simple, streamlined work around has been prepared. -
Resolución
In order to solve the situation the following steps are needed:1. Configure the SQL server in single user mode. To do this, open SQL Server Configuration Manager and identify the instance to work with.2. From the SQL Server Configuration Manager, stop the desired SQL Server Instance3. In the SQL Server Configuration Manageropen the properties of the desired SQL Server Instance by choosing them from the mouse right-click context and navigate to the Advanced tab.4. Identify the Startup parameter and open it. Add-m"sqlcmd.exe";at the beginning of the line. This will allow only sqlcmd.exe to connect to the SQL instance (thus blocking other applications that may otherwise take over the single user SQL instance).5. Start the SQL instance6. On the SQL server, open an elevated command prompt.7. Runsqlcmd.exe -S .\instancenamePlease note that the "-S" parameter needs to be capital letter8. Run a Transact-SQL (Below) command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add or update.EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';GO9. Reconfigure the SQL server to multiuser mode (Use SQL Server Configuration Manager to stop instance service, navigate to properties, Advanced, remove the '-m"sqlcmd.exe";' string from the Startup parameter and restart the service).10. If the SQL server is in mixed mode, reset the SA password using the Microsoft SQL Server Management Studio, loging in with the account you added, expanding the Security and then logins and finally by opening the properties and reseting the password.All done!