January 31, 2010 at 9:11 pm
HI ALL,
I have removed the BuiltIn\Administrators group from SQL Server 2005.
Now I dont have any access to the SQL Server.
Also the SQL Server has been configured for the Windows Authentication Mode only.
Now i need to get the access to the SQL Server and add back the above group to the sysadmin role.
Also please note that i dont have the SA Password set as the SQL Server has been configured for the Windows Authentication only.
Please do let me know to achieve the above.
January 31, 2010 at 10:11 pm
If you don't have a windows account with administrative access, I suggest you find the media for SQL Server and perform an uninstall/reinstall.
January 31, 2010 at 11:31 pm
Actually, since you are running SQL Server 2005, if yoour account has SysAdmin rights you can still can access to your system. You will need to restart SQL Server in single user mode. Not sure how to do it right off the top of my head, but if you check Books Online, you should be able to find the necessary switch settings.
February 1, 2010 at 3:32 am
February 1, 2010 at 4:02 am
Jeetendra,
as Lynn stated, start the SQL Server in single user mode.
When you are administrator on the server, you will have sysadmin rights on the SQL Server, no matter what.
HTH
Robbert
February 1, 2010 at 8:54 am
Robert,
Is that true? In that case, security is being bypassed for single user mode. That's a big hole. If you don't have rights, you shouldn't be able to connect in Single user mode.
February 1, 2010 at 8:57 am
Requires restarting SQL Server from the command line using a switch (check with K Brian Kelley (sp ?)) which means you need access to the server; actually physically or using RDP.
February 1, 2010 at 9:06 am
Have you tried this: http://www.sqlservercentral.com/articles/Administration/68271/
How to Connect to a SQL 2005 Server When You Are Completely Locked Out
By Rudy Panigas, 2009/11/03
February 1, 2010 at 9:31 am
From http://msdn.microsoft.com/en-us/library/ms188236.aspx
Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, see Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.
February 1, 2010 at 9:36 am
Robbert,
Thanks, didn't realize that. In essence, I think that means that all your encryption security is gone if someone can get Admin access to the box. A hole I didn't realize.
February 1, 2010 at 1:01 pm
Before deleting builtin\Administrators anytime, it is important to find the members in this group.
You can get from below query itself
xp_logininfo 'Builtin\Administrators','members'
Just make sure that the account that runs your services are explicity added, otherwise, it is risky to delete builtin\Administrators.
M&M
February 2, 2010 at 2:32 am
Steve Jones - Editor (2/1/2010)
Thanks, didn't realize that. In essence, I think that means that all your encryption security is gone if someone can get Admin access to the box. A hole I didn't realize.
I wouldn't exactly call it a hole--if someone you don't trust has Admin access to your server then them being able to gain access to the SQL Server by restarting it in single-user mode is the least of your worries, I should think!
February 2, 2010 at 2:48 am
After you have restarted SQL Server in single user mode (as described above) you can add the login BUILTIN\Administrators to get your access rights back.
It is best practice to set up a Windows group to contain the logins for the DBA team. You then create a login for the group and grant that group Sysadmin rights. After this you should delete the BUILTIN\Administrators login so that admin access to SQL Server is properly controlled. (Typically, far too many people have local admin rights.)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 2, 2010 at 3:02 am
February 2, 2010 at 7:39 am
paul.knibbs (2/2/2010)
I wouldn't exactly call it a hole--if someone you don't trust has Admin access to your server then them being able to gain access to the SQL Server by restarting it in single-user mode is the least of your worries, I should think!
Not sure I agree. Separation of jobs mandated by SOX, and a good idea. You don't necessarily want domain admins running SQL Server, but this allows that. It also means that a generic account (Administrator) can get access to your data. Not great for auditing.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply