THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

Think before unchecking sysadmin rights of BUILTIN\Administrators.

Hello every body. This is my first blog on that great site so I am really exciting.

I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to another 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. As Raul said that 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

Published Monday, February 08, 2010 2:58 AM by Uri Dimant

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Howarth said:

Another option is to simply add your local account to the local group to which the SQL Server service account belongs.

e.g.

SQLServer2008MSSQLUser$LAPTOP$MSSQLSERVER

Chris

February 8, 2010 7:39 AM
 

Uri Dimant said:

Chris

SQL Server service was running under Local Admin group.So simple adding your account to the group won't work. Remember, there is no domain controller..

And simple forcing SQL Server running under local account does not work because it has no appropiate permissions.

February 8, 2010 8:16 AM
 

TalaT said:

That's cool.

I faced a situation like this on a production machine running sql 2000.

However that solution only works for sql2k, not later.

http://talatnauman.blogspot.com/2008/04/how-to-login-to-sql-server-as-sa-while.html

February 9, 2010 3:08 AM
 

Alexander Kuznetsov said:

Welcome aboard, Uri!

February 17, 2010 3:50 PM
 

sudhir said:

This probably is very late comment but just wondering how enabling DAC  would have helped in this case?? because all the admin accounts were disabled. so no one can really connect to the sql server using DAC either.

March 6, 2013 1:29 AM

Leave a Comment

(required) 
(required) 
Submit

About Uri Dimant

Uri Dimant
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement