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

From: BP Margolin (bpmargo@attglobal.net)
Date: 08/23/02 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Thu, 22 Aug 2002 20:41:24 -0400

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: login security question
    ... > I have an application which connects to the SQL server. ... Use Enterprise Manager to access the database / roles. ... Give the Application Role the appropriate permissions. ... what the stored proc is called) using the secret password for the App Role ...
    (microsoft.public.sqlserver.server)
  • Re: Login with no Fixed Server Role and DB Role can stop SQL Agent Service?
    ... a Window 2000 Login with Domain User default permissions, ... > actually answered the question about the permissions the user has re: ... Forget about SQL Server for the moment. ... >> Enterprise Manager, but he is still able to stop the SQL Agent ...
    (microsoft.public.sqlserver.security)
  • invalid value specified for STOPAT parameter
    ... I too had problems when trying to perform a Point in Time restore ... Enterprise Manager, I receive a error message "Invalid value specified ... "YDM" by SQL Server where as the value supplied by the SQL Server ... Select "British English", then hit OK. ...
    (microsoft.public.sqlserver.server)
  • Re: Login with no Fixed Server Role and DB Role can stop SQL Agent Service?
    ... If it turns out to be a bug in Enterprise Manager, ... > a Window 2000 Login with Domain User default permissions, ... Forget about SQL Server for the moment. ...
    (microsoft.public.sqlserver.security)
  • RE: Upgrading to SQL 2005 ENT
    ... I left the reporting services off. ... It gives a good step by step how to on upgrading to SQL Server 2005. ... some SQL Server features will not be available for installation. ... It has the same version from the Enterprise Manager. ...
    (microsoft.public.sqlserver.server)