REPOST:SQL Login with no Fixed Server Role and DB Role can stop SQL Agent Service?

From: Johnathen Liew (johnliew@rocketmail.com)
Date: 08/27/02 

From: "Johnathen Liew" <johnliew@rocketmail.com>
Date: Tue, 27 Aug 2002 12:00:53 +0800

Hi Guys,

Thanks for taking notice for my case, I really appreciate it everyone,
especially BP and Richard.

I had checked all the user rights, and everything seems to be proper...but
user still can stop services.

Let me elaborate the situation more clearly, maybe this issue seems a bit
more complicated then it seems.

We have one database server, running SQL 2000 Enterprise Edition (SP2) on
Windows 2000 Server (SP2). This server is a standalone server, it does not
join any domain, and is residing on the WORKGROUP. There are no additional
USERs, just those built-in, like "Administrator", "Guest" and so on,
everything is left as default.

A person is designated to do some maintenance on the SQL Server by running a
pre-programmed DTS package. This person has a user on the DOMAIN (e.g.
DOMAIN1\User1), and he is using this Domain User to login to his Windows XP
Professional machine, which is joined to the domain. We installed SQL Server
Client Tools and Connectivity to this machine.

We created a SQL Login (e.g. SQL1), and we checked that there are no Fixed
Server Role and Database Role selected. The designated person registers the
SQL Server on his Enterprise Manager using this SQL Login (SQL1). He is able
to execute the DTS package successfully, but we noticed that he is able to
stop SQL Server Agent Service as well. We asked him to try to do other tasks
in Enterprise Manager, like returning queries and managing objects
(tables/view), but all the options are greyed out, which is good. But we
asked him to confirm that he is able to stop SQL Server Agent, and he is
able to start and stop that service thru Enterprise Manager.

Could anyone try to simulate this?

Thanks a lot!

Johnathen Liew

"BP Margolin" <bpmargo@attglobal.net> wrote in message
news:uRqZwlGTCHA.3896@tkmsftngp11...
> Whoops ... just noticed that you actually posted on Saturday.
>
> But you know it's also acceptable to take the occasional Saturday off as
> well ;-)
>
> BPM
>
> "BP Margolin" <bpmargo@attglobal.net> wrote in message
> news:uz2qYdGTCHA.2336@tkmsftngp08...
> > Richard,
> >
> > Thanks !
> >
> > And BTW, it's Sunday ... it's acceptable for MS personnel to take the
> > **occasional" Sunday off :-)
> >
> > BPM
> >
> > "Richard Waymire [MS]" <rwaymi_ms@microsoft.com> wrote in message
> > news:uRx0q09SCHA.2308@tkmsftngp09...
> > > Only if the user really doesn't have rights - but in all honesty I'd
bet
> > > money the user does have the rights to control services granted
somehow.
> > >
> > > --
> > > Richard Waymire, MCSE, MCDBA
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "BP Margolin" <bpmargo@attglobal.net> wrote in message
> > > news:eeoqOI6SCHA.2556@tkmsftngp11...
> > > > Richard,
> > > >
> > > > Thanks for the information ... then this is indeed a bug, right :-(
> > > >
> > > > BPM
> > > >
> > > > "Richard Waymire [MS]" <rwaymi_ms@microsoft.com> wrote in message
> > > > news:#zszp85SCHA.1864@tkmsftngp12...
> > > > > Yup - somehow the user has windows security rights to control
> > services -
> > > > we
> > > > > just call the win32 APIs to control services as the user.
> > > > >
> > > > > --
> > > > > Richard Waymire, MCSE, MCDBA
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > > "BP Margolin" <bpmargo@attglobal.net> wrote in message
> > > > > news:eYFvP$pSCHA.1644@tkmsftngp08...
> > > > > > Johnathen,
> > > > > >
> > > > > > Well, I'm out of ideas ... sorry ;-(
> > > > > >
> > > > > > If no one else chimes in, you might consider opening a case with
> > > > Microsoft
> > > > > > Product Support Services. If it turns out to be a bug in
> Enterprise
> > > > > Manager,
> > > > > > then PSS should not charge you.
> > > > > >
> > > > > > -------------------------------------------
> > > > > > BP Margolin
> > > > > > Please reply only to the newsgroups.
> > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
> etc.)
> > > > which
> > > > > > can be cut and pasted into Query Analyzer is appreciated.
> > > > > >
> > > > > > "Johnathen Liew" <johnliew@rocketmail.com> wrote in message
> > > > > > news:e#fkdinSCHA.3552@tkmsftngp08...
> > > > > > > BP,
> > > > > > >
> > > > > > > I guess I didn't clearly specify the rights of the user. The
> user
> > is
> > > > > > holding
> > > > > > > a Window 2000 Login with Domain User default permissions,
> > therefore
> > > he
> > > > > is
> > > > > > > not suppose to stop any of the services of my SQL Server 2000.
> > > > > > >
> > > > > > > Thanks.
> > > > > > >
> > > > > > > Johnathen
> > > > > > >
> > > > > > > "BP Margolin" <bpmargo@attglobal.net> wrote in message
> > > > > > > news:O$yHp1jSCHA.1496@tkmsftngp11...
> > > > > > > > Johnathen,
> > > > > > > >
> > > > > > > > I did a quick review of this thread, and unless I'm mistaken
> you
> > > > never
> > > > > > > > actually answered the question about the permissions the
user
> > has
> > > > re:
> > > > > > the
> > > > > > > > operating system. Forget about SQL Server for the moment.
What
> > are
> > > > the
> > > > > > > > permissions for the user's Windows login? Would the user,
> > > completely
> > > > > > aside
> > > > > > > > from Enterprise Manager, be able to successfully issue a
"net
> > > stop"
> > > > > for
> > > > > > > the
> > > > > > > > SQL Server Agent Services from a command prompt?
> > > > > > > >
> > > > > > > > -------------------------------------------
> > > > > > > > BP Margolin
> > > > > > > > Please reply only to the newsgroups.
> > > > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT
...,
> > > etc.)
> > > > > > which
> > > > > > > > can be cut and pasted into Query Analyzer is appreciated.
> > > > > > > >
> > > > > > > > "Johnathen Liew" <johnliew@rocketmail.com> wrote in message
> > > > > > > > news:enf0UrZSCHA.1644@tkmsftngp08...
> > > > > > > > > BP,
> > > > > > > > >
> > > > > > > > > As I said, the user uses the limited login to register the

> SQL
> > > > > server
> > > > > > on
> > > > > > > > his
> > > > > > > > > Enterprise Manager, but he is still able to stop the SQL
> Agent
> > > > > > > > Services....
> > > > > > > > >
> > > > > > > > > Any ideas?
> > > > > > > > >
> > > > > > > > > Johnathen
> > > > > > > > > "BP Margolin" <bpmargo@attglobal.net> wrote in message
> > > > > > > > > news:Ov$ZuwTSCHA.3360@tkmsftngp11...
> > > > > > > > > > Johnathen,
> > > > > > > > > >
> > > > > > > > > > Thanks for the additional information.
> > > > > > > > > >
> > > > > > > > > > Check the login used to register SQL Server within
> > Enterprise
> > > > > > Manager
> > > > > > > > ...
> > > > > > > > > >
> > > > > > > > > > Right-click the server name, choose Properties, choose
> "Edit
> > > SQL
> > > > > > > Server
> > > > > > > > > > Registration properties ..."
> > > > > > > > > >
> > > > > > > > > > -------------------------------------------
> > > > > > > > > > BP Margolin
> > > > > > > > > > Please reply only to the newsgroups.
> > > > > > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT
> > ...,
> > > > > etc.)
> > > > > > > > which
> > > > > > > > > > can be cut and pasted into Query Analyzer is
appreciated.
> > > > > > > > > >
> > > > > > > > > > "Johnathen Liew" <johnliew@rocketmail.com> wrote in
> message
> > > > > > > > > > news:uKqBGJNSCHA.3736@tkmsftngp11...
> > > > > > > > > > > Hi BP,
> > > > > > > > > > >
> > > > > > > > > > > Sorry for the lack of exact information. This
restricted
> > > user
> > > > is
> > > > > > > > suppose
> > > > > > > > > > to
> > > > > > > > > > > connect thru the SQL Server by means of SQL Client
Tools
> > and
> > > > > > > > > Connectivity.
> > > > > > > > > > > He will use Enterprise Manager to execute the DTS
> package.
> > > We
> > > > > > found
> > > > > > > > out
> > > > > > > > > > > that, he is able to stop the SQL Agent Service by
going
> > into
> > > > > > > > Enterprise
> > > > > > > > > > > Manager, right-clicking the SQL Agent Service, and
stop
> > it.
> > > > This
> > > > > > > user
> > > > > > > > is
> > > > > > > > > > > holding a SQL login, and is not holding any Windows
2000
> > > login
> > > > > in
> > > > > > > the
> > > > > > > > > SQL
> > > > > > > > > > > Server.
> > > > > > > > > > >
> > > > > > > > > > > Any ideas?
> > > > > > > > > > >
> > > > > > > > > > > Johnathen
> > > > > > > > > > >
> > > > > > > > > > > "BP Margolin" <bpmargo@attglobal.net> wrote in message
> > > > > > > > > > > news:O8PQ$jLSCHA.1648@tkmsftngp08...
> > > > > > > > > > > > Johnathen,
> > > > > > > > > > > >
> > > > > > > > > > > > You might indicate in the future the exact process
by
> > > which
> > > > > the
> > > > > > > user
> > > > > > > > > is
> > > > > > > > > > > able
> > > > > > > > > > > > to stop the SQL Server Agent service.
> > > > > > > > > > > >
> > > > > > > > > > > > It sorta sounds as if you are mixing SQL Server
> > > permissions
> > > > > with
> > > > > > > > that
> > > > > > > > > of
> > > > > > > > > > > the
> > > > > > > > > > > > operating system.
> > > > > > > > > > > > Stopping a service ... regardless if it is the SQL
> Agent
> > > > > > service,
> > > > > > > or
> > > > > > > > > any
> > > > > > > > > > > > other ... is a function of the rights of the user
> > defined
> > > on
> > > > > the
> > > > > > > > > > operating
> > > > > > > > > > > > system.
> > > > > > > > > > > >
> > > > > > > > > > > > To express this another way ... the SA is god within
> SQL
> > > > > Server,
> > > > > > > > > right.
> > > > > > > > > > > > Well, unless the SA has the requisite operating
system
> > > > > > > permissions,
> > > > > > > > > the
> > > > > > > > > > SA
> > > > > > > > > > > > can NOT start the SQL Server service. (BTW, just to
> > > > completely
> > > > > > > > > accurate,
> > > > > > > > > > > the
> > > > > > > > > > > > SA can stop SQL Server via the SHUTDOWN command,
even
> if
> > > the
> > > > > SA
> > > > > > > > would
> > > > > > > > > > not
> > > > > > > > > > > > normally have the operating system permissions to
stop
> > the
> > > > SQL
> > > > > > > > Server
> > > > > > > > > > > > service.)
> > > > > > > > > > > >
> > > > > > > > > > > > Review the operating system rights granted the user.
> > > > > > > > > > > >
> > > > > > > > > > > > -------------------------------------------
> > > > > > > > > > > > BP Margolin
> > > > > > > > > > > > Please reply only to the newsgroups.
> > > > > > > > > > > > When posting, inclusion of SQL (CREATE TABLE ...,
> INSERT
> > > > ...,
> > > > > > > etc.)
> > > > > > > > > > which
> > > > > > > > > > > > can be cut and pasted into Query Analyzer is
> > appreciated.
> > > > > > > > > > > >
> > > > > > > > > > > > "Johnathen Liew" <johnliew@rocketmail.com> wrote in
> > > message
> > > > > > > > > > > > news:uvvEeaLSCHA.1756@tkmsftngp11...
> > > > > > > > > > > > > Hi,
> > > > > > > > > > > > >
> > > > > > > > > > > > > We have a scenerio, where we need to create a DTS
> > > package,
> > > > > > which
> > > > > > > > is
> > > > > > > > > > run
> > > > > > > > > > > by
> > > > > > > > > > > > a
> > > > > > > > > > > > > designated user. This user should have no other
> rights
> > > > other
> > > > > > > than
> > > > > > > > > > > running
> > > > > > > > > > > > > the DTS package. We created a login, with no Fixed
> > > Server
> > > > > > Roles
> > > > > > > > and
> > > > > > > > > no
> > > > > > > > > > > > > Database Roles. This user is able to execute the
> > > package,
> > > > > but
> > > > > > he
> > > > > > > > is
> > > > > > > > > > able
> > > > > > > > > > > > to
> > > > > > > > > > > > > stop the SQL Agent services as well, which is bad,
> but
> > > he
> > > > > > cannot
> > > > > > > > > > > > drop/create
> > > > > > > > > > > > > tables, which is good.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Is this a SQL Server bug? Any idea anyone?
> > > > > > > > > > > > >
> > > > > > > > > > > > > We are using SQL Server 2000 Enterprise Edition
with
> > > SP2.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks
> > > > > > > > > > > > > Johnathen Liew
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Donna Lambert [MS]"
<dlambert@online.microsoft.com>
> > > wrote
> > > > > in
> > > > > > > > > message
> > > > > > > > > > > > > news:I0wk$8URCHA.2468@cpmsftngxa06...
> > > > > > > > > > > > > > Adrian,
> > > > > > > > > > > > > > Seems like it would be much simpler to password
> > > protect
> > > > > your
> > > > > > > dts
> > > > > > > > > > > > packages.
> > > > > > > > > > > > > > Just a suggestion.
> > > > > > > > > > > > > > Donna Lambert
> > > > > > > > > > > > > > Microsoft SQL Server Support
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Disclaimer:
> > > > > > > > > > > > > > This posting is provided "AS IS" with no
> warranties,
> > > and
> > > > > > > confers
> > > > > > > > > no
> > > > > > > > > > > > > rights.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Are you secure? For information about the
> Microsoft
> > > > > > Strategic
> > > > > > > > > > > Technology
> > > > > > > > > > > > > > Protection Program and to order your FREE
Security
> > > Tool
> > > > > Kit,
> > > > > > > > > please
> > > > > > > > > > > > visit
> > > > > > > > > > > > > > http://www.microsoft.com/security.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Recent viruses on the Internet underscore the
> threat
> > > to
> > > > > all
> > > > > > > > > computer
> > > > > > > > > > > > users
> > > > > > > > > > > > > > and highlight challenges facing the entire
> industry
> > in
> > > > > > > providing
> > > > > > > > > > > > security
> > > > > > > > > > > > > > that everyone needs to conduct business. I
> encourage
> > > you
> > > > > to
> > > > > > > sign
> > > > > > > > > up
> > > > > > > > > > to
> > > > > > > > > > > > > > receive automatic notification of Microsoft
> Security
> > > > > > Bulletins
> > > > > > > > by
> > > > > > > > > > > > visiting
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/
> > > > > > > > > > > > > > bulletin/notify.asp. For more information on
> > security,
> > > > our
> > > > > > > > > Strategic
> > > > > > > > > > > > > > Technology Protection Program and to order your
> FREE
> > > > > > Security
> > > > > > > > Tool
> > > > > > > > > > > Kit,
> > > > > > > > > > > > > > please visit http://www.microsoft.com/security.
We
> > > will
> > > > be
> > > > > > > happy
> > > > > > > > > to
> > > > > > > > > > > > answer
> > > > > > > > > > > > > > any questions or provide assistance with your
> > security
> > > > > > needs.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --------------------
> > > > > > > > > > > > > > | Content-Class: urn:content-classes:message
> > > > > > > > > > > > > > | From: "Adrian" <adrianw@persoft.com.my>
> > > > > > > > > > > > > > | Sender: "Adrian" <adrianw@persoft.com.my>
> > > > > > > > > > > > > > | Subject: DTS Security
> > > > > > > > > > > > > > | Date: Thu, 15 Aug 2002 20:29:53 -0700
> > > > > > > > > > > > > > | Lines: 8
> > > > > > > > > > > > > > | Message-ID:
> > > > > <2d4401c244d5$2fa91c50$35ef2ecf@TKMSFTNGXA11>
> > > > > > > > > > > > > > | MIME-Version: 1.0
> > > > > > > > > > > > > > | Content-Type: text/plain;
> > > > > > > > > > > > > > | charset="iso-8859-1"
> > > > > > > > > > > > > > | Content-Transfer-Encoding: 7bit
> > > > > > > > > > > > > > | X-Newsreader: Microsoft CDO for Windows 2000
> > > > > > > > > > > > > > | X-MimeOLE: Produced By Microsoft MimeOLE
> > > > V5.50.4910.0300
> > > > > > > > > > > > > > | Thread-Index: AcJE1S+pE2PllW9uQampwdIwCabugg==
> > > > > > > > > > > > > > | Newsgroups:
microsoft.public.sqlserver.security
> > > > > > > > > > > > > > | Path: cpmsftngxa06
> > > > > > > > > > > > > > | Xref: cpmsftngxa06
> > > > > > microsoft.public.sqlserver.security:7577
> > > > > > > > > > > > > > | NNTP-Posting-Host: TKMSFTNGXA11 10.201.226.39
> > > > > > > > > > > > > > | X-Tomcat-NG:
microsoft.public.sqlserver.security
> > > > > > > > > > > > > > |
> > > > > > > > > > > > > > | Hi,
> > > > > > > > > > > > > > | I want to create a user id where this id can
> > only
> > > > run
> > > > > > > > > > > > > > | DTS. Other function like starting of the SQL
> > Agent,
> > > > > backup
> > > > > > > > > > > > > > | database should not be given access right.
Could
> > > > anyone
> > > > > > > > > > > > > > | help what type rights should i assign to this
> > userid
> > > .
> > > > > > > > > > > > > > |
> > > > > > > > > > > > > > | Thanks
> > > > > > > > > > > > > > | Adrian
> > > > > > > > > > > > > > |
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: file rights issue...
    ... Domain Admin has rights to everything so not being able to access the ... The Terminal Server is an entirely different ... of BV we are running uses an SQL DB engine called Pervasive SQL to ... the accounting data on the Windows 2000 server through the pervasive ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: file rights issue...
    ... files over to the WIn2K machine where domain rights are now being applied. ... The Terminal Server is an entirely different issue. ... BV we are running uses an SQL DB engine called Pervasive SQL to access ... Windows 2000 SQL server - which is also where the accounting data is ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: file rights issue...
    ... of the domain so no domain rights were being used - just the local NT ... The Terminal Server is an entirely different issue. ... of BV we are running uses an SQL DB engine called Pervasive SQL to ... our Windows 2000 SQL server - which is also where the accounting data is ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: file rights issue...
    ... part of the domain so no domain rights were being used - just the local ... The Terminal Server is an entirely different issue. ... of BV we are running uses an SQL DB engine called Pervasive SQL to ... our Windows 2000 SQL server - which is also where the accounting data ...
    (microsoft.public.backoffice.smallbiz2000)
  • REPOST:SQL Login with no Fixed Server Role and DB Role can stop SQL Agent Service?
    ... I had checked all the user rights, and everything seems to be proper...but ... running SQL 2000 Enterprise Edition on ... Windows 2000 Server. ... SQL Server on his Enterprise Manager using this SQL Login. ...
    (microsoft.public.sqlserver.security)