sp_sqlagent_has_server_access returns incorrect results when BUILTIN\Administrators are removed - by CR8N

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 387521 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/10/2008 4:54:24 PM
Access Restriction Public


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')
                  FROM master.dbo.syslogins
                  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
        FROM master.dbo.syslogins
        WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')

Sign in to post a comment.
Posted by Robert Spinelli 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.

Problem Description
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

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.

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.
Posted by Microsoft on 1/6/2009 at 10:32 AM
Thanks for the bug. We will look into it.