SQL Server Home
sp_sqlagent_has_server_access returns incorrect results when BUILTIN\Administrators are removed
as By Design
12/10/2008 4:54:24 PM
User(s) can reproduce this bug
Hopefully I am not going crazy here, but I have removed the BUILTIN\Administrators login and explicitly added NT AUTHORITY\SYSTEM as a Login with the sysadmin role.
My SQL Server Agent service is running as localsystem.
I noticed my jobs that were owned by NT AUTHORITY\SYSTEM were failed with an error of: The owner (NT AUTHORITY\SYSTEM) of job <job name> does not have server access.
I tracked it down to the stored procedure msdb.dbo.sp_sqlagent_has_server_access which has a special case for NT AUTHORITY\SYSTEM in the code:
IF (UPPER(@login_name collate SQL_Latin1_General_CP1_CS_AS) = N'NT AUTHORITY\SYSTEM')
IF (EXISTS (SELECT *
WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')))
SELECT @has_server_access = hasaccess,
@is_sysadmin = sysadmin,
@actual_login_name = loginname
WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')
SQL Server 2005 SP2 - Developer Edition
Tools (SSMS, Agent, Profiler, etc.)
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
ADD BUILTIN\ADMINISTRATORS LOGIN
ADD NT AUTHORITY\SYSTEM LOGIN WITH SYSADMIN ROLE
REMOVE BUILTIN\ADMINISTRATORS LOGIN
SET SQL SERVER AGENT TO RUN AS LOCALSYSTEM
CREATE A JOB WITH NT AUTHORITY\SYSTEM AS THE JOB OWNER
EXECUTE THE JOB
THE JOB FAILED
The job succeeded.
to post a comment.
Please enter a comment.
on 3/31/2009 at 12:07 PM
Here is the info I got from MS in regards to this:
We further investigated why sysadmin privileged 'NT AUTHORITY\SYSTEM' was not getting server access.
requirement as per our auditing policies that we cannot keep 'BUILTIN\ADMINISTRATORS' group in SQL server. Also another requirement for the ours would be that all the jobs would be owned
by N'NT AUTHORITY\SYSTEM'.
We have removed 'BUILTIN\ADMINISTRATORS' Group from sql server and we also specifically assigned sysadmin privileges for N'NT AUTHORITY\SYSTEM'
After the changes are done whenever we try to execute the jobs that are owned by 'NT AUTHORITY\SYSTEM' or when it runs as per the schedule the Jobs fail with the Below Error Message.
The owner (NT AUTHORITY\SYSTEM) of job after remove does not have server access.
Research At our End
We were able to reproduce the problem in-house and in our research we have found that the jobs run fine for 29min after we remove 'BUILTIN\ADMINISTRATORS', Once we cross 29min the job starts to fail.
Running a Profiler Trace in Background reveled the below findings.
We internally execute sp_sqlagent_has_server_access, This is the Exact statement
EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'NT AUTHORITY\SYSTEM'
Looking at the stored procedure found the reason why we hit into this problem whenever we have deleted 'BUILTIN\ADMINISTRATORS'
Whenever this stored procedure is executed we delete entry from syscachedcredentials table where DATEDIFF(MINUTE, cachedate, GETDATE()) >= 29 which means we delete data older than 29 minutes.
Later we set specific values to some variables intentionally
-- Set defaults
SELECT @has_server_access = 0
SELECT @is_sysadmin = 0
SELECT @actual_login_name = FORMATMESSAGE(14205)
FORMATMESSAGE(14205) will make the @actual_login_name varaiable hold the value " (unknown) "
Later there is a logical check in the stored procedure
-- update the cache only if something is found
IF (UPPER(@actual_login_name collate SQL_Latin1_General_CP1_CS_AS) <> '(UNKNOWN)')
Inside this IF condition we update syscachedcredentials if data exists else we insert data into the syscachedcredentials.We dont enter this logical code since we have another check condition above this code for 'NT AUTHORITY\SYSTEM' and due to that we follow a different code path and never insert or update data into the table resulting in the above error message since the stored procedure returns the below data eventhough NT AUTHORITY\SYSTEM is a sysadmin on the sql server instance.
has_server_access is_sysadmin actual_login_name
----------------- ----------- ------------------
0 0 (unknown)
The only way to resolve the issue would be to add 'BUILTIN\ADMINISTRATORS' back to sql server,Our test has also reveled because if we add 'BUILTIN\ADMINISTRATORS' and then later remove it ,The job would only execute
fine for next 29min. The moment we pass on this time line job starts to fail.
Also if we manually add data into the table it would start working fine for next 29min
insert into syscachedcredentials SELECT N'NT AUTHORITY\SYSTEM',1,1,GETDATE()
Next Action Plans
1) add back builtin\administrators. but according to our corporate policy I don’t think that’s possible.
2) add an entry every a few minutes in msdb.dbo.syscachedcredentials. since this is our system table, we shouldn't do this without further investigation.
3) use a different user account for owner.
So its a bug in my opinion. MS did say this is fixed in SQL2008.
I ended up just using SA for the job owner for all the maint tasks instead of nt authority\system.
on 1/6/2009 at 10:32 AM
Thanks for the bug. We will look into it.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft