Retrieving Admin Credentials for SQL Server Database Backup

Question

Connecting to a database from SQL Server Management Studio with normal authentication is not allowing backup operations. An ex-employee who was the admin did not provide any credentials. As a result, backup cannot be taken, and attempts to assign roles like DB_backup_operator are unsuccessful, with the backup option not appearing using normal credentials. What steps can be taken to retrieve the admin credentials and ensure a full backup of the database? Alternative methods, such as exporting to a BACPAC file, have not been helpful.

Answer 1

Hi Mohana Reddy,

Thanks for reaching out to Microsoft Q&A.

Please follow below steps:

Connect to SQL in a Single User mode. Reference document- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-ver16

Create a new SQL server admin account - Reference document : https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver16

CREATE LOGIN TempLogin WITH PASSWORD = '<strong_password>';

ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;

Perform Backup as per this document- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server?view=sql-server-ver16

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi @Mohana Reddy

This article describes how you can regain access to the SQL Server Database Engine as a system administrator if you're locked out. A system administrator can lose access to an instance of SQL Server due to one of the following reasons:

• All logins that are members of the sysadmin fixed server role were removed by mistake.
• All Windows Groups that are members of the sysadmin fixed server role were removed by mistake.
• The logins that are members of the sysadmin fixed server role are for individuals who left the company or who aren't available.
• The account is disabled, or no one knows the password.sa

You may follow the steps to get the sysadmin permission to back up your database.

Connect to SQL Server when system administrators are locked out

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

What steps can be taken to retrieve the admin credentials

You can't, of course, passwords are not stored in clear text.

You tagged your post with SQL Server Azure + on-premise; so which one are you using, they are different?