Rights Change to Administrator Account Causes Hassles

I am trying to tighten up security in my database(s) following
suggestions from the MS advanced SQL admin class 2723. The first thing I
tried, since I know it is not used, is to remove the system admin rights
from the Administrator login account. This is on SQL2ksp3a.
The SQL jobs in my database are owned by SQLAdmin, which is a systems
admin account. The SQL agent service starts under local account. I
removed the system admin rights from the Administrator login, and all of
the the maintenance plan jobs stopped working. I get this error: "unable
to determine if owner of transaction has server access - execute
permission denied on
sp_sqlagent_has_server_access database msdb owner dbo sqlstate 42000
error 229"
Since Administrator is not the owner of the job, or the account that the
service starts in, or the owner of msdb, why does this happen? I cannot
find any connection to the Administrator login that would cause this to
happen. The Administrator login is not used for anything in the database
that I have seen.
I had this question posted in another forum, which may have been the
wrong venue for it. The only reply I received was from Andy Svendsen
(Thanks, Andy!) who suggested checking the "Run As User" option on each
job. All the jobs run as self. He also suggested checking any Active X
or steps that run a command shell in the jobs in question. All of the
jobs are log & db backup, optimization, and integrity check jobs created
with the maintenance plan wizard.
I tried recreating each job, to no avail. I have had to return sysadmin
rights to the Administrator login for my jobs to run.
Thanks in advance for any ideas what else I can try!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Karen, A few questions for you:
I'm not clear on why you are removing the sysadmin rights from the
Administrator login account? (I know you want to lock SQL Server down). Did
you define a "user" account that you have given appropriate permissions for
the services, NTFS and registry?
Are you referring to the 'sa' account or the local NT 'Administrator'
account?
If the latter, is that the account that you are running the MSSQLServer and
SQL Server Agent services?
Steve
"Karen R" <hgkritchey@.hotmail.com> wrote in message
news:#J7p#k60DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> I am trying to tighten up security in my database(s) following
> suggestions from the MS advanced SQL admin class 2723. The first thing I
> tried, since I know it is not used, is to remove the system admin rights
> from the Administrator login account. This is on SQL2ksp3a.
> The SQL jobs in my database are owned by SQLAdmin, which is a systems
> admin account. The SQL agent service starts under local account. I
> removed the system admin rights from the Administrator login, and all of
> the the maintenance plan jobs stopped working. I get this error: "unable
> to determine if owner of transaction has server access - execute
> permission denied on
> sp_sqlagent_has_server_access database msdb owner dbo sqlstate 42000
> error 229"
> Since Administrator is not the owner of the job, or the account that the
> service starts in, or the owner of msdb, why does this happen? I cannot
> find any connection to the Administrator login that would cause this to
> happen. The Administrator login is not used for anything in the database
> that I have seen.
> I had this question posted in another forum, which may have been the
> wrong venue for it. The only reply I received was from Andy Svendsen
> (Thanks, Andy!) who suggested checking the "Run As User" option on each
> job. All the jobs run as self. He also suggested checking any Active X
> or steps that run a command shell in the jobs in question. All of the
> jobs are log & db backup, optimization, and integrity check jobs created
> with the maintenance plan wizard.
> I tried recreating each job, to no avail. I have had to return sysadmin
> rights to the Administrator login for my jobs to run.
> Thanks in advance for any ideas what else I can try!
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

|||Hi Steve, Thanks for the response. I did post a reply the same day as
yours, but it looks like it never made it!
I want to remove the SQL sysadmin rights from the NT Administrator login
account, not sa. I have an separate NT admin login that I use for
sysadmin access. As to the "why," besides my goal of tightening up SQL
security, it is recommended in the MS advanced SQL admin class 2723, and
also because the job structure here makes it necessary.
Currently all services run under the "local" account. I will be changing
this to a specific account, but one thing at a time! All the jobs
run as "self." Most of the jobs are maintenance plan jobs, and are
owned by my SQL admin account, not Administrator or sa OR dbo.
This has been very frustrating, and I would like to be able to make some
headway without having to reinstall SQL.
Thanks for all advice/help/insight in advance!
Karen
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||To remove the NT Admins Take a look at the following :
Microsoft SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
Implementation of Server Level Security and Object Level Security
Remove the BUILTIN\Administrators Server Login
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/SP3SEC02.ASP
One you've created the new accounts used to start the MSSQLServer service,
you'll need to add them using the SQL Server Enterprise manager or grant
them these specific rights.
283811 HOW TO: Change the SQL Server or SQL Server Agent Service Account
http://support.microsoft.com/?id=283811
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||The other item that I missed is that the SQL Maint jobs are typically run
by members of the sysadmin group. So, if the job owner isn't a member of
sysadmin, then these jobs may fail.
So, although SQLAgent doesn't need to be run by a system admin account, the
job step may require greater privledges.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Thanks for all the ideas - all good resources. I am still fuzzy on why
it happened, since nothing depended upon the Administrator account. No
jobs were changed, and the sql admin account that they run under is part
of the sysadmin group. I'll keep trying, though I can't allow the jobs
to fail for our critical apps. I guess I'll have to take a weekend to
play with this until I can resolve it.
Thanks again!
Karen
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!