 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Do local administrators on a system with SQL Server 2005 installed have an implied 'sysadmin' right to SQL Server?
Gurus, Is it true that local administrators on a system with SQL Server 2005 installed have an implied 'sysadmin' right to SQL Server?  SignatureSpin > Gurus, > > Is it true that local administrators on a system with SQL Server 2005 > installed have an implied 'sysadmin' right to SQL Server? simple: YES ! > simple: YES ! How can I stop this behavior? >> simple: YES ! > > How can I stop this behavior? 1 first, be sure you give some windows account (yours maybe) sysadmin rights on sql server!!! 2 then, check 1 again!!! 3 then, remove BUILTIN\administrators login from logins on SQL server > > simple: YES ! > > How can I stop this behavior? It just so happens that I had to figure out how to disable this myself just last week. I worked on SQL 2000, but hopefully this will work on 2005 as well. I put this into a .sql file and called it using osql, in this way I was able to scipt this change and execute it on our hundreds of different servers very quickly. However, you could just drop this into query analyzer (built into EM in 2005 I recall) and run it: exec sp_denylogin [BUILTIN\administrators] That is it, that will deby local admin access to MSSQL. I also scripted the changing of the sa password if you need that it is: exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa' Hope that helps! > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@inet.hr> wrote in message > [quoted text clipped - 20 lines] > > Hope that helps! OH! Something to look out for though. If you deny login access to the Windows Admin group then the SQL Agent service will stop running and say it cannot connect. By default it will be connecting using a local Windows Admin account. That will have to be changed (you can right click it in EM and go to properties and change it). I found a script for that as well, but it is rather long and I don't even understand the whole thing. It works though! If you need that as well I can put it in here. <<OH! Something to look out for though. If you deny login access to the Windows Admin group then the SQL Agent service will stop running and say it cannot connect. By default it will be connecting using a local Windows Admin account. >> Agent do not run under an Admin account by default. It will run using the account you specify in setup, and can also change later on using SAQL Server Configuration Manager. The handling of service account is done the same way and tools for SQL Server service as SQL Server Agent service. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > On Mar 12, 7:29 am, "Spin" <S...@spin.com> wrote: > [quoted text clipped - 22 lines] > > Hope that helps! OH! Something to look out for though. If you deny login access to the Windows Admin group then the SQL Agent service will stop running and say it cannot connect. By default it will be connecting using a local Windows Admin account. That will have to be changed (you can right click it in EM and go to properties and change it). I found a script for that as well, but it is rather long and I don't even understand the whole thing. It works though! If you need that as well I can put it in here. On Mar 14, 2:37 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > <<OH! Something to look out for though. If you deny login access to the > Windows Admin group then the SQL Agent service will stop running and [quoted text clipped - 45 lines] > > - Show quoted text - Thanks for the clarification Tibor! I thought it was by default simply because I see it that way on all our systems, but since they were all installed the same way that only means it is by default for me, not for the actual software. I guess he wasn't concerned about that though since there was no response. Then againk, I am more then a little slow to check things myself. << Thanks for the clarification Tibor! I thought it was by default simply because I see it that way on all our systems, but since they were all installed the same way that only means it is by default for me, not for the actual software. >> that is what I thought. The main reason I replied was just to not leave this thread hanging with a statement what can be read as the product being "not as secure" by default. :-) SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi On Mar 14, 2:37 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > <<OH! Something to look out for though. If you deny login access to the > Windows Admin group then the SQL Agent service will stop running and [quoted text clipped - 52 lines] > > - Show quoted text - Thanks for the clarification Tibor! I thought it was by default simply because I see it that way on all our systems, but since they were all installed the same way that only means it is by default for me, not for the actual software. I guess he wasn't concerned about that though since there was no response. Then againk, I am more then a little slow to check things myself. I haven't seen anyone mention those pesky default local accounts that set themselves up as sysadmin. %servername%\SQLServer2005SQLAgentUser$%servername%$MSSQLSERVER %servername%\SQLServer2005MSSQLUser$%servername%$MSSQLSERVER It seems that the default setup puts the service account into the later. All anyone with enough power would have to do is drop an account into one of these and presto! What are all of the implications of taking these roles out of the picture (or at least out of admin)? Where is the best documentation for fixing this? > << Thanks for the clarification Tibor! I thought it was by default > simply because I see it that way on all our systems, but since they [quoted text clipped - 69 lines] > that though since there was no response. > Then againk, I am more then a little slow to check things myself. err groups. I meant groups instead of accounts. > I haven't seen anyone mention those pesky default local accounts that set > themselves up as sysadmin. [quoted text clipped - 81 lines] > > that though since there was no response. > > Then againk, I am more then a little slow to check things myself. Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL Server2005SP2). >> Gurus, >> >> Is it true that local administrators on a system with SQL Server 2005 >> installed have an implied 'sysadmin' right to SQL Server? > simple: YES ! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.