Post reply

credential/proxy

  • June 30, 2013 at 8:05 pm

    #279368

    I have a proc as below

    ALTER PROCEDURE [dbo].[sp_proc_test]

    WITH EXECUTE AS 'ABCD\svcaccount'

    AS

    EXEC msdb.dbo.sp_start_job @job_name = 'JB_ABC_Run'

    GO

    I setup a proxy account for windows login and mapped a SQL login to it. When I try to execute through a SQL login, I am getting following error.

    The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

    I have made sure the windows (ABCD\svcaccount) login has full access (owner) to the msdb db.

    What am I missing?

  • July 5, 2013 at 10:19 am

    #1630135

    Anybody can shed light on this would be much appreciated.

  • July 5, 2013 at 11:44 am

    #1630153

    i don't see in what you posted where you granted ABCD\svcaccount permissions in msdb to the procedure sp_start_job; you might have glossed over it, but did you do something like this?

    USE msdb;

    CREATE USER [ABCD\svcaccount] FOR LOGIN [ABCD\svcaccount];

    GRANT EXECUTE ON sp_start_job TO [ABCD\svcaccount];

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 5, 2013 at 1:30 pm

    #1630184

    When you use EXECUTE AS, you impersonate a database user, not a server login and you are sandboxed inside your database. You can break out of the sandbox - and open a security hole while you are it.

    I have a longer article on my web site that discusses various techniques to grant permissions through stored procedures, and it includes two examples of how to solve exactly this problem, see http://www.sommarskog.se/grantperm.html

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply