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.
Showing posts with label administrators. Show all posts
Showing posts with label administrators. Show all posts
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?
--
SpinSpin wrote:
> 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 !|||"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?|||Spin wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>> 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|||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:
>> 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 !|||On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
> > 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!|||On Mar 13, 9:13=A0am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message=
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > 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. =A0I 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. =A0I 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.|||<<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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > 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!
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=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. =A0If you deny login access to the=
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By 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 Manage=r. The handling of service
> account is done the same way and tools for SQL Server service as SQL Serve=r Agent service.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in messa=ge
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > 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!
> OH! Something to look out for though. =A0If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. =A0That 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. =A0It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - 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. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
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
> 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.
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > 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!
> 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.- Hide quoted text -
> - 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?
"Tibor Karaszi" wrote:
> << 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. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tonagon" <tony.roberts@.marriott.com> wrote in message
> news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> 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
> > 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.
> >
> > --
> > Tibor Karaszi, SQL Server
> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> >
> > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> >
> >
> >
> >
> >
> > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> >
> > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >
> > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> >
> > > > > 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!
> >
> > 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.- Hide quoted text -
> >
> > - 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.
>|||err groups. I meant groups instead of accounts.
"Cory" wrote:
> 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?
>
> "Tibor Karaszi" wrote:
> > << 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. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tonagon" <tony.roberts@.marriott.com> wrote in message
> > news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> > 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
> > > 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.
> > >
> > > --
> > > Tibor Karaszi, SQL Server
> > > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> > >
> > > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> > >
> > > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> > >
> > >
> > >
> > >
> > >
> > > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > >
> > > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >
> > > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > >
> > > > > > 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!
> > >
> > > 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.- Hide quoted text -
> > >
> > > - 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.
> >
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
--
SpinSpin wrote:
> 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 !|||"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?|||Spin wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>> 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|||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:
>> 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 !|||On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
> > 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!|||On Mar 13, 9:13=A0am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message=
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > 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. =A0I 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. =A0I 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.|||<<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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > 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!
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=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. =A0If you deny login access to the=
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By 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 Manage=r. The handling of service
> account is done the same way and tools for SQL Server service as SQL Serve=r Agent service.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in messa=ge
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > 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!
> OH! Something to look out for though. =A0If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. =A0That 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. =A0It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - 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. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
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
> 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.
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > 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!
> 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.- Hide quoted text -
> - 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?
"Tibor Karaszi" wrote:
> << 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. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tonagon" <tony.roberts@.marriott.com> wrote in message
> news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> 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
> > 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.
> >
> > --
> > Tibor Karaszi, SQL Server
> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> >
> > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> >
> >
> >
> >
> >
> > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> >
> > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >
> > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> >
> > > > > 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!
> >
> > 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.- Hide quoted text -
> >
> > - 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.
>|||err groups. I meant groups instead of accounts.
"Cory" wrote:
> 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?
>
> "Tibor Karaszi" wrote:
> > << 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. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tonagon" <tony.roberts@.marriott.com> wrote in message
> > news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> > 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
> > > 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.
> > >
> > > --
> > > Tibor Karaszi, SQL Server
> > > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> > >
> > > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> > >
> > > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> > >
> > >
> > >
> > >
> > >
> > > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > >
> > > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >
> > > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > >
> > > > > > 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!
> > >
> > > 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.- Hide quoted text -
> > >
> > > - 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.
> >
Monday, March 19, 2012
DMO & .NET: How to get default database
I have a SQL2K instance that has granted me access through an unusual setup.
The SQL2K instance has granted me access through BUILTIN\Administrators by
including "NT_Authority\Authenticated Users" as part of that Windows Group.
Based on that, using DMO and C#, I need to be able to determine the default
database context. Normally, using DMO, you can access the Database property
off of the Login object, however, since my login name "DOMAIN\lgrissom" is
NOT explicitly defined anywhere, that won't work. Any ideas appreciated.
Lee
I found "SELECT DB_NAME()" T-SQL command, and used ADO.NET to execute that
against my ADO.NET connection instead of relying on my hidden DMO
connection. Works great.
Lee
The SQL2K instance has granted me access through BUILTIN\Administrators by
including "NT_Authority\Authenticated Users" as part of that Windows Group.
Based on that, using DMO and C#, I need to be able to determine the default
database context. Normally, using DMO, you can access the Database property
off of the Login object, however, since my login name "DOMAIN\lgrissom" is
NOT explicitly defined anywhere, that won't work. Any ideas appreciated.
Lee
I found "SELECT DB_NAME()" T-SQL command, and used ADO.NET to execute that
against my ADO.NET connection instead of relying on my hidden DMO
connection. Works great.
Lee
Sunday, February 19, 2012
Distributing a Custom SQL Server Enterprise Manager MMC with Server Registrations
Is it possible to make available to several administrators a custom
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
Eric
It used to work! Check here for some vb and java scripts:
http://groups.google.com.au/groups?h...adm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den%26lr%3D%2 6ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks for the reply! At your suggestion, I found the following message
which contained an effective solution:
From: "Mingqing Cheng [MSFT]" (v-mingqc@.online.microsoft.com)
Subject: RE: Export/Import SQL EM Registrations?
View: Complete Thread (6 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.tools
Date: 2004-07-16 00:25:54 PST
Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I
understood
you? If there is anything I misunderstood, please feel free to let me
know
Based on my scope, The method by exporting Register Files is not
supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the
following.
Please substitute [servername] with SQL Server name and [password] with
the
SA password.
It also creates a table RegServers in pubs database so make sure that
this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the
SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with
the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer )
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries
in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions
or
concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
Eric
It used to work! Check here for some vb and java scripts:
http://groups.google.com.au/groups?h...adm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den%26lr%3D%2 6ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks for the reply! At your suggestion, I found the following message
which contained an effective solution:
From: "Mingqing Cheng [MSFT]" (v-mingqc@.online.microsoft.com)
Subject: RE: Export/Import SQL EM Registrations?
View: Complete Thread (6 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.tools
Date: 2004-07-16 00:25:54 PST
Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I
understood
you? If there is anything I misunderstood, please feel free to let me
know
Based on my scope, The method by exporting Register Files is not
supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the
following.
Please substitute [servername] with SQL Server name and [password] with
the
SA password.
It also creates a table RegServers in pubs database so make sure that
this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the
SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with
the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer )
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries
in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions
or
concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Distributing a Custom SQL Server Enterprise Manager MMC with Server Registrations
Is it possible to make available to several administrators a custom
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
& #91;HKEY_CURRENT_USER\Software\Microsoft
\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
EricIt used to work! Check here for some vb and Java scripts:
http://groups.google.com.au/groups?...eadm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den
%26lr%3D%26ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks for the reply! At your suggestion, I found the following message
which contained an effective solution:
From: "Mingqing Cheng [MSFT]" (v-mingqc@.online.microsoft.com)
Subject: RE: Export/Import SQL EM Registrations?
View: Complete Thread (6 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.tools
Date: 2004-07-16 00:25:54 PST
Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I
understood
you? If there is anything I misunderstood, please feel free to let me
know
Based on my scope, The method by exporting Register Files is not
supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the
following.
Please substitute [servername] with SQL Server name and [password] w
ith
the
SA password.
It also creates a table RegServers in pubs database so make sure that
this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the
SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] wi
th
the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer)
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries
in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Mic
rosoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions
or
concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
& #91;HKEY_CURRENT_USER\Software\Microsoft
\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
EricIt used to work! Check here for some vb and Java scripts:
http://groups.google.com.au/groups?...eadm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den
%26lr%3D%26ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks for the reply! At your suggestion, I found the following message
which contained an effective solution:
From: "Mingqing Cheng [MSFT]" (v-mingqc@.online.microsoft.com)
Subject: RE: Export/Import SQL EM Registrations?
View: Complete Thread (6 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.tools
Date: 2004-07-16 00:25:54 PST
Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I
understood
you? If there is anything I misunderstood, please feel free to let me
know
Based on my scope, The method by exporting Register Files is not
supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the
following.
Please substitute [servername] with SQL Server name and [password] w
ith
the
SA password.
It also creates a table RegServers in pubs database so make sure that
this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the
SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] wi
th
the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer)
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries
in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Mic
rosoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions
or
concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Distributing a Custom SQL Server Enterprise Manager MMC with Server Registrations
Is it possible to make available to several administrators a custom
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
EricIt used to work! Check here for some vb and java scripts:
http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&threadm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den%26lr%3D%26ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
SQL Server Enterprise Manager MMC containing 80 server registrations?
I would like to avoid having to manually recreate the console on
multiple machines. I tried distributing the .MSC file and exporting
and importing the following registry key, but only the group names
appeared in the MMC, not the servers:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
Any suggestions?
Thanks,
EricIt used to work! Check here for some vb and java scripts:
http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&threadm=PqyaNXwaEH
A.2900%40cpmsftngxa06.phx.gbl&rnum=4&prev=/groups%3Fq%3Denterprise%2Bman
ager%2Bregistry%2Bsql%2Bimport%26hl%3Den%26lr%3D%26ie%3DUTF-8
HTH,
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)