none
Strange SQL server permission behavior RRS feed

  • Question

  • Hi Experts,

    I am facing a peculiar issue with SQL Server 2005 SP2 system. Here is the scenario

    1. I login to SQL server using a windows account which has sysadmin permission and is part of local administrators group on the sql server.
    2. I execute vendor stored proc say sproc_multiply within a user database. It gives me an error message

    Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
    The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

    3. Then, I changed my connection to use sql login which has sysadmin permission and dbo permission on user database. I was able to execute the same stored procedure.

    4. After that, I again change my connection to use the same windows account as in step1, it executes successfully now...no error.
    5. I disconnect from sql server and log back in again as windows account and again it gives the same error message??

    Is this a bug? Am I missing something? I have tried the following
    a. Granting exclusive execute permission on object master.dbo. xp_sqlagent_enum_jobs to windows user. No luck

    b. Replacing username with windows user account I am using. No luck.
    use master
    go
    grant select on master.dbo.sysperfinfo to {username}
    go
    grant execute on master.dbo.xp_sqlagent_notify to {username}
    go
    grant execute on master.dbo.xp_sqlagent_enum_jobs to {username}
    go
    grant execute on master.dbo.xp_sqlagent_param to {username}
    go
    grant execute on master.dbo.xp_sqlagent_is_starting to {username}
    go
    grant execute on master.dbo.xp_instance_regenumvalues to {username}
    go
    use msdb
    go
    grant execute on msdb.dbo.sp_help_alert to {username}
    go
    grant execute on msdb.dbo.sp_help_notification to {username}
    go
    grant select on msdb.dbo.sysalerts to {username}
    go
    grant select on msdb.dbo.sysoperators to {username}
    go
    grant select on msdb.dbo.sysnotifications to {username}
    go




    Thursday, October 22, 2009 8:31 AM

All replies

  • Can you please provide us with more information? We would like to know if the stored procedure you are calling is doing any impersonation or EXEC AS or anything like that. Also, which of Agent’s SPs are returning the error?

      Thanks a lot,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, October 23, 2009 9:41 PM
    Moderator