Tuesday, January 12, 2010

Think before unchecking sysadmin rights of BUILTIN\Administrators.

I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to anotgher account.

That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.
However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.
Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.

The solution we found was to start SQL Server with single user mode. Using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx