SQL Server SP4 bug(?) when deleting SQL User

• Tomasz Swiecki

  SSC Veteran

  Points: 216

  More actions

  January 4, 2007 at 5:40 am

  #171434

  Hi all,

  I have a problem with SP_DROPLOGIN. When I run it logged as a user that has only securityadmin role it throws an error:

  Server: Msg 229, Level 14, State 5, Line 1

  SELECT permission denied on object 'sysjobs', database 'msdb', owner 'dbo'.

  It turned out that it is caused by msdb..sp_check_for_owned_jobs, In Sql Server Service Pack 4 there is "SELECT COUNT(*) FROM msdb.dbo.sysjobs......" while in SP3 and lower we have "SELECT COUNT(*) FROM msdb.dbo.sysjobs_view"

  sysjobs table does not have public read rights but sysjobs_view has them.

  To fix this I have to give msdb.sysjobs public read.

  The question is how to fix it without changing msdb.sysjobs permissions?

  Best Regards

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738693

  More actions

  January 8, 2007 at 9:28 am

  #681809

  I'd call MS support for this. Sounds like maybe they either changed functionality or it's a bug in the SP.