locked
The SELECT permission was denied on the object 'syscategories', database 'msdb', schema 'dbo'. RRS feed

  • Question

  • Hi all,

    I have a single select statement to monitor JOB status at database msdb, it works perfectly at versions 2000, 2005 and 2008 but in version 2012 got denied access to views syscategories, sysjobactivity, sysjobhistory, sysjobs and sysjobsteps even having applied "grant select on" to user (principals) at database msdb.

    Anyone have seen this and found an solution?

    --- SQL Server Version

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
        Oct 19 2012 13:38:57
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    --- My Query

    set nocount on
    select x.job_name, x.job_status, x.monitor_status from (
    select j.name job_name
         , case
             when datediff(minute,s.login_time,current_timestamp) >= 8
                then 'In progress (more than 8h) '
             when datediff(minute,s.login_time,current_timestamp) >= 24
                then 'In progress (more than 24h) '
             else 'In progress'
           end job_status
         , case
             when datediff(minute,s.login_time,current_timestamp) >= 8
                then 8 -- 'In progress (more than 8h) '
             when datediff(minute,s.login_time,current_timestamp) >= 24
                then 9 -- 'In progress (more than 24h) '
             else 7 -- 'In progress'
           end monitor_status
      from sys.dm_exec_sessions s
             join msdb.dbo.sysjobs j
                on master.dbo.fn_varbintohexstr(convert(varbinary(16), j.job_id))COLLATE Latin1_General_CI_AI
                 = substring(replace(s.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
             inner join msdb.dbo.syscategories c
                on c.category_id = j.category_id
     where s.program_name like '%SQLAGENT - TSQL JOBSTEP%'
       and c.name like 'REPL-%'
    union all
    select j.name
         , case
              when datediff(minute,current_timestamp,ja.next_scheduled_run_date) <= -10
                 then 'Delayed'
              else
                 case jh.run_status
                    when 0 then
                       case when lower(left(j.name,8)) = 'uoldiveo'
                          then 'Failed (admin)'
                          else 'Failed'
                       end
                    when 1 then 'Succeeded'
                    when 2 then 'Retry'
                    when 3 then
                       case when lower(left(j.name,8)) = 'uoldiveo'
                          then 'Cancelled (admin)'
                          else 'Cancelled'
                       end
                    when 4 then 'In progress'
                 end
           end
         , case
              when datediff(minute,current_timestamp,ja.next_scheduled_run_date) <= -10
                 then 0 -- Delayed
              else
                 case jh.run_status
                    when 0 then
                       case when lower(left(j.name,8)) = 'uoldiveo'
                          then 1 -- 'Failed (admin)'
                          else 2 -- 'Failed'
                       end
                    when 1 then 3 -- 'Succeeded'
                    when 2 then 4 -- 'Retry'
                    when 3 then
                       case when lower(left(j.name,8)) = 'uoldiveo'
                          then 5 -- 'Cancelled (admin)'
                          else 6 -- 'Cancelled'
                       end
                    when 4 then 7 -- 'In progress'
                 end
           end
      from (msdb.dbo.sysjobactivity ja left join msdb.dbo.sysjobhistory jh on ja.job_history_id = jh.instance_id)
           join msdb.dbo.sysjobs j on ja.job_id = j.job_id
     where ja.session_id=(select max(session_id) from msdb.dbo.sysjobactivity where job_id = ja.job_id)
            and j.enabled = 1
            and jh.run_status <= 3
    ) x

    Tuesday, April 22, 2014 2:42 PM

Answers

  • Hi,

    We found what is the problem, the user account at database is orphan.

    At SQL 2012 when executed statement "create user [mon] for login [mon];" he left empty the "default schema" where when executed sql statement, he not knows what schema is to parse. You see this after apply the command "sp_change_users_logins 'Report'".

    So the solution was apply "sp_change_users_login" with option "Update_One" to user "mon".

    I believe that's an detail for the first version of SQL 2012 because in version SQL2012 SP1 not occurs. To solve definitely was included at command to create user the clause "with default_schema = [dbo]"

    Many thanks for help.

    Romolo.

    • Marked as answer by Romolo Alves Wednesday, April 23, 2014 6:52 PM
    Wednesday, April 23, 2014 6:51 PM

All replies

  • I was able to run the below without problems on SQL 2012:

    USE master
    CREATE LOGIN ove WITH PASSWORD = 'ÖLKJLKJ?="#'
    GRANT VIEW SERVER STATE TO ove
    go
    USE msdb
    go
    CREATE USER ove

    GRANT SELECT ON syscategories TO ove
    GRANT SELECT ON sysjobactivity TO ove
    GRANT SELECT ON sysjobhistory TO ove
    GRANT SELECT ON sysjobs TO ove
    GRANT SELECT ON sysjobsteps TO ove
    go
    EXECUTE AS LOGIN = 'ove'
    -- your query here
    REVERT
    go
    DROP USER ove
    go
    USE tempdb
    go
    DROP LOGIN ove

    Have you checked that there is no active DENY in force?

    Rather than granting these permissions, you could package this in a stored procedure that you signed with a certificate and then grant a login and user create from the certificate the required permissions. I discuss this technique in detail in an article on my web site:
    http://www.sommarskog.se/grantperm.html
    (But certs will not help you against DENY.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by tracycai Wednesday, April 23, 2014 6:14 AM
    Tuesday, April 22, 2014 9:24 PM
  • Yeap, i've checked about DENY permission but it not exist, the user that ran the query has these privileges:

    use msdb;
    grant select on backupset to mon;
    grant select on syscategories to mon;
    grant select on sysjobactivity to mon;
    grant select on sysjobhistory to mon;
    grant select on sysjobs to mon;
    grant select on sysjobsteps to mon;

    use master;
    grant execute on sys.fn_varbintohexstr to mon;
    grant view any definition to mon;
    grant view any database to mon;
    grant view server state to mon;
    go

    And was created a login for each database with this:

    exec sp_MSforeachdb @command1 = N'use [?]; if not exists (select 1 from sys.database_principals where name=N''mon') create user [mon] for login [mon];';

    I search for an solution to execute at current user without the "impersonate" privilege.


    Romolo.

    Wednesday, April 23, 2014 1:36 PM
  • Hi,

    We found what is the problem, the user account at database is orphan.

    At SQL 2012 when executed statement "create user [mon] for login [mon];" he left empty the "default schema" where when executed sql statement, he not knows what schema is to parse. You see this after apply the command "sp_change_users_logins 'Report'".

    So the solution was apply "sp_change_users_login" with option "Update_One" to user "mon".

    I believe that's an detail for the first version of SQL 2012 because in version SQL2012 SP1 not occurs. To solve definitely was included at command to create user the clause "with default_schema = [dbo]"

    Many thanks for help.

    Romolo.

    • Marked as answer by Romolo Alves Wednesday, April 23, 2014 6:52 PM
    Wednesday, April 23, 2014 6:51 PM
  • I would suspect that the reason the user was orphaned, was because the database had been restored from another instance, and thus already existed.

    By the way, you don't need the undocumented fn_varbintohexstr, but you can use convert with styles 1 or 2 to convert a binary value to a hex string. (Style 1 will include 0x, style 2 will not.)

    You mention something about impersonation. I don't know exactly what this refers to here, but maybe certificate signing is a better method for you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 23, 2014 9:17 PM
  • Hi,

    This case is about a deploy on an existing environment, the user was created after all database creation.

    In my environment has an application that the objective is to connect on instance with read-only privileges on metadata, so this made possible to apply without risk at our datacenter (about one thousand managed instances).

    For now, my problem is solved.

    About Impersonate, I mentioned about documentation: "To specify the EXECUTE AS clause when you create or modify a module, you must have IMPERSONATE permissions on the specified principal and also permissions to create the module. You can always impersonate yourself. When no execution context is specified or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required." http://technet.microsoft.com/en-US/us-en/library/ms188354.aspx

    Thanks for your help.


    Romolo.

    Thursday, April 24, 2014 3:00 PM