Need a sysadmin login

We’re running mixed mode authentication on our SQL Servers.To make the server “safer” builtin\admininstrators no longer have sysadmin role on the sql server.If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it?How could we reset the password or create a new sa account with a new password?This situation has not occurred, but I’m worried about how to recover from it should it occur.This question relates to SQL 2000 and SQL 2005.

David Zokaites,

DBA & Software Engineer

If you deleted the users and have no available sysadmin user you will be locked out of your server for the sysadmin work. Therefore I always suggest to create a user which is not used and only available for these scenarios. Depending on the kind of security level the password can be stored somewhere in a safe or at a secure place.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the information. I thought the scenario might lock me out of sysadmin on the sql server. I will certainly make sure we have a secondary sysadmin account to prevent such a disaster.

David

|||

Jens is right as far as SQL Server 2000 is concerned. For SQL Server 2000, you can get locked out; if that would happen, you can recover other databases than master by reattaching them on a different SQL Server instance.

However, in SQL Server 2005, by restarting the server in single-user mode, a local administrator can get access to SQL Server as a sysadmin, so you cannot get yourself locked out of the SQL Server instance.

Thanks
Laurentiu