New Sysadmin cannot stop SQL instance...

Currently, I'm running SQL Server 2000 on Windows 2000
Advanced Server. I created a new Windows user who is not
in the Administrators group. Then, I entered into the SQL
instance's properties and changed the startup account to
be this new user's credentials. As a result, SQL created a
login for this new user and added him to the System
Administrators server role. When I log into the system as
this new user, I'm able to start the instance but I'm not
able to stop it. I don't understand this behavior since a
System administrator has SHUTDOWN privileges. Any
assistance is greatly appreciated.
Sincerely,
ABGYou will need to give the user rights to start/stop services.
Many dbas have administrative access on the windows boxes
used as sql servers.
"ABG" <abimael.garcia@.unisys.com> wrote in message
news:060501c34a59$86410a20$a101280a@.phx.gbl...
> Currently, I'm running SQL Server 2000 on Windows 2000
> Advanced Server. I created a new Windows user who is not
> in the Administrators group. Then, I entered into the SQL
> instance's properties and changed the startup account to
> be this new user's credentials. As a result, SQL created a
> login for this new user and added him to the System
> Administrators server role. When I log into the system as
> this new user, I'm able to start the instance but I'm not
> able to stop it. I don't understand this behavior since a
> System administrator has SHUTDOWN privileges. Any
> assistance is greatly appreciated.
> Sincerely,
> ABG
>|||Thank you for your response. I'm using Windows 2000
Advanced Server and I don't see the user right (start/stop
service). I do see (Logon as a service) and the user
already has that privilege. I purposely don't want to make
this user an administrator of the system but I do want him
to be able to shutdown the SQL Server instance. Do you
have any other suggestions?
>--Original Message--
>You will need to give the user rights to start/stop
services.
>Many dbas have administrative access on the windows boxes
>used as sql servers.
>"ABG" <abimael.garcia@.unisys.com> wrote in message
>news:060501c34a59$86410a20$a101280a@.phx.gbl...
>> Currently, I'm running SQL Server 2000 on Windows 2000
>> Advanced Server. I created a new Windows user who is not
>> in the Administrators group. Then, I entered into the
SQL
>> instance's properties and changed the startup account to
>> be this new user's credentials. As a result, SQL
created a
>> login for this new user and added him to the System
>> Administrators server role. When I log into the system
as
>> this new user, I'm able to start the instance but I'm
not
>> able to stop it. I don't understand this behavior since
a
>> System administrator has SHUTDOWN privileges. Any
>> assistance is greatly appreciated.
>> Sincerely,
>> ABG
>
>.
>|||Unfortunately I don't know what rights you need to assign, but
would question why you believe you cannot trust the person
responsible for corporate data with your network.
"ABG" <abimael.garcia@.unisys.com> wrote in message
news:017201c34bb9$248932b0$a301280a@.phx.gbl...
> Thank you for your response. I'm using Windows 2000
> Advanced Server and I don't see the user right (start/stop
> service). I do see (Logon as a service) and the user
> already has that privilege. I purposely don't want to make
> this user an administrator of the system but I do want him
> to be able to shutdown the SQL Server instance. Do you
> have any other suggestions?
>
>
>
> >--Original Message--
> >You will need to give the user rights to start/stop
> services.
> >
> >Many dbas have administrative access on the windows boxes
> >used as sql servers.
> >
> >"ABG" <abimael.garcia@.unisys.com> wrote in message
> >news:060501c34a59$86410a20$a101280a@.phx.gbl...
> >>
> >> Currently, I'm running SQL Server 2000 on Windows 2000
> >> Advanced Server. I created a new Windows user who is not
> >> in the Administrators group. Then, I entered into the
> SQL
> >> instance's properties and changed the startup account to
> >> be this new user's credentials. As a result, SQL
> created a
> >> login for this new user and added him to the System
> >> Administrators server role. When I log into the system
> as
> >> this new user, I'm able to start the instance but I'm
> not
> >> able to stop it. I don't understand this behavior since
> a
> >> System administrator has SHUTDOWN privileges. Any
> >> assistance is greatly appreciated.
> >> Sincerely,
> >> ABG
> >>
> >
> >
> >.
> >|||This is purely for testing and research purposes. I'm
trying to determine security best practices for multiple
SQL instance scenarios. In a SQL consolidation case,
politics becomes an issue: one department owns one
instance and another department may govern another. So,
internal security and isolated administration becomes an
issue. Basically, I was trying to setup a case where there
are completely separate sysadmins for each instance
inwhich they cannot affect the operations of the other
instances i.e. startup/shutdown the other instances.
I wanted a sysadmin of an instance to be able to
perform all tasks on his instance including
startup/shutdown. But I didn't want that sysadmin to have
full administrative rights on the whole system. Now it
appears that he must be an Administrator of the system in
order to do this. And this is very interesting because by
default the Builtin\Administrators windows group is added
to the System Administrators role. Therefore, if all
sysadmin users from all instances are also a part of the
Administrators windows group then they can essentially
perform administrative activities on the other instances.
And this is what I'm trying to prevent. Thank you for all
your help.
Sincerely,
ABG
>--Original Message--
>Unfortunately I don't know what rights you need to
assign, but
>would question why you believe you cannot trust the person
>responsible for corporate data with your network.
>"ABG" <abimael.garcia@.unisys.com> wrote in message
>news:017201c34bb9$248932b0$a301280a@.phx.gbl...
>> Thank you for your response. I'm using Windows 2000
>> Advanced Server and I don't see the user right
(start/stop
>> service). I do see (Logon as a service) and the user
>> already has that privilege. I purposely don't want to
make
>> this user an administrator of the system but I do want
him
>> to be able to shutdown the SQL Server instance. Do you
>> have any other suggestions?
>>
>>
>>
>> >--Original Message--
>> >You will need to give the user rights to start/stop
>> services.
>> >
>> >Many dbas have administrative access on the windows
boxes
>> >used as sql servers.
>> >
>> >"ABG" <abimael.garcia@.unisys.com> wrote in message
>> >news:060501c34a59$86410a20$a101280a@.phx.gbl...
>> >>
>> >> Currently, I'm running SQL Server 2000 on Windows
2000
>> >> Advanced Server. I created a new Windows user who is
not
>> >> in the Administrators group. Then, I entered into the
>> SQL
>> >> instance's properties and changed the startup
account to
>> >> be this new user's credentials. As a result, SQL
>> created a
>> >> login for this new user and added him to the System
>> >> Administrators server role. When I log into the
system
>> as
>> >> this new user, I'm able to start the instance but I'm
>> not
>> >> able to stop it. I don't understand this behavior
since
>> a
>> >> System administrator has SHUTDOWN privileges. Any
>> >> assistance is greatly appreciated.
>> >> Sincerely,
>> >> ABG
>> >>
>> >
>> >
>> >.
>> >
>
>.
>