do log files also store select statement?Do they store any info wrt indexes. If I had clustered and non clustered
indexes , does the tran log backup files hold any info about the indexes.
Will i see reduced size in log file backups if i drop the indexes
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:08bc01c35c85$9fdf4080$a301280a@.phx.gbl...
> Nope. You can not rollback a select statement and it is
> not a transaction. TO see the current log you can issue
> select * from ::fn_dblog(null,null)
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> >--Original Message--
> >
> >
> >.
> >|||Hassan
The transaction logs capture all changes to your database,
as Ray said they don't record selects (except select into)
because they don't change the database.
When you insert, update or delete as well as making
changes to the data you can be making changes to the
indexes. So yes by dropping indexes (although the drop
will by caught by the log), you will reduce your logging a
small amount.
However indexes are usually there to aid performance, I
would be very careful about droping indexes to keep the
log smaller. The impact on your system is likely to be
higher than the small saving in log space. I would not
advise it unless you perform a very good audit of your
indexes and find that some are not being used.
If you are having problems with your log size, you may be
better off with performing transaction log backups more
often.
Hope this helps
John
Tuesday, March 27, 2012
Do locks slow other processes?
Hi,
Lets say I have a process running on the server (e.g a stored proc) -
Process A
Then another scheduled process starts to run but is locked up by process A.
Will process A run any slower as it's holding up the second process, or,
does it not affect the performance at all?
Basically, I'm quite happy for the second process to have to wait - but - I
don't want the performance to be radically slowed.
ThanksLondon
http://www.sql-server-performance.com/reducing_locks.asp
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>|||Process A should not be slowed by processes which are waiting on A.
Offcourse if there are more processes which are running or claiming memory,
process A can get slowed. A waiting process should consume very little (or
no) cpu. It is offcourse in a list of processes so the OS uses a very very
VERY small amount of CPU to check or pass over this process when
rescheduling, SQL-server has to set a wait on a lock this consumes very VERY
VERY small amount of cpu.
Offcourse if the process which is blocked by A holds locks and process A
needs those resources you wil have a deadlock. This holds both processes
till a deadlock is detected, one process is 'aborted' the other can
continue.
ben brugman
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>sql
Lets say I have a process running on the server (e.g a stored proc) -
Process A
Then another scheduled process starts to run but is locked up by process A.
Will process A run any slower as it's holding up the second process, or,
does it not affect the performance at all?
Basically, I'm quite happy for the second process to have to wait - but - I
don't want the performance to be radically slowed.
ThanksLondon
http://www.sql-server-performance.com/reducing_locks.asp
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>|||Process A should not be slowed by processes which are waiting on A.
Offcourse if there are more processes which are running or claiming memory,
process A can get slowed. A waiting process should consume very little (or
no) cpu. It is offcourse in a list of processes so the OS uses a very very
VERY small amount of CPU to check or pass over this process when
rescheduling, SQL-server has to set a wait on a lock this consumes very VERY
VERY small amount of cpu.
Offcourse if the process which is blocked by A holds locks and process A
needs those resources you wil have a deadlock. This holds both processes
till a deadlock is detected, one process is 'aborted' the other can
continue.
ben brugman
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>sql
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.
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.
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.
> >
Do Linked Servers REQUIRE SA rights
I am trying to set up a linked server, and one of the user is a SQL
user. This user only has permission to select data from several tables.
When I attempt to query the remote server I receive following message:
Access to the remote server is denied because no log in-mapping exists.
However when I grant the user SA rights on the local server that user
is able to query the data.
Any thoughts ?Accessing a linked server doesn't necessarily require
someone to be a member of the sysadmins role. You need to
check the login mapping as the error suggests - look at the
security tab for the linked server.
-Sue
On 8 Jun 2005 12:41:48 -0700, "Rafet Ducic"
<rducic@.gmail.com> wrote:
>I am trying to set up a linked server, and one of the user is a SQL
>user. This user only has permission to select data from several tables.
>
>When I attempt to query the remote server I receive following message:
>Access to the remote server is denied because no log in-mapping exists.
>
>However when I grant the user SA rights on the local server that user
>is able to query the data.
>Any thoughts ?
user. This user only has permission to select data from several tables.
When I attempt to query the remote server I receive following message:
Access to the remote server is denied because no log in-mapping exists.
However when I grant the user SA rights on the local server that user
is able to query the data.
Any thoughts ?Accessing a linked server doesn't necessarily require
someone to be a member of the sysadmins role. You need to
check the login mapping as the error suggests - look at the
security tab for the linked server.
-Sue
On 8 Jun 2005 12:41:48 -0700, "Rafet Ducic"
<rducic@.gmail.com> wrote:
>I am trying to set up a linked server, and one of the user is a SQL
>user. This user only has permission to select data from several tables.
>
>When I attempt to query the remote server I receive following message:
>Access to the remote server is denied because no log in-mapping exists.
>
>However when I grant the user SA rights on the local server that user
>is able to query the data.
>Any thoughts ?
Do Inserts into Temptable enforce order of select?
I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM temptable without an order by, the first record that is displayed as an id of 113. Then about the 42nd record in the temp table is the ID=1 record. In addition, the table records are not ordered by the original order of the select that inserted into the temp table.
From this I am led to believe that SQL does not enforce the orders of records on INSERT into statesments. Is this a correct assumption, or might there be something else going on?Please take a look at the blog post below:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Ordering is not guaranteed for SELECT queries unless you include an explicit ORDER BY in the outer-most query. Similarly, the order of insertion of rows is not guranteed either. The identity values are however generated based on the order specified in the ORDER BY in INSERT...SELECT statement.
do indexed views slow down inserts
sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql
Subscribe to:
Posts (Atom)