Tuesday, March 27, 2012

Do local administrators on a system with SQL Server 2005 installed have an implied 'sysad

Gurus,
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
Spin
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?
|||Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL
Server2005SP2).
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> Spin wrote:
> simple: YES !
|||On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>
> 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!
|||On Mar 13, 9:13Xam, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
>
>
> It just so happens that I had to figure out how to disable this myself
> just last week. XI 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. XI also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> 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.

No comments:

Post a Comment