Post reply

Impersonation in a Proc but not for the user running it

  • April 21, 2011 at 3:23 pm

    #236209

    I probably missed it somewhere but I haven't been able to find it. Here's what I'd like to do but don't know how to setup for it....

    I have the following stored procedure, as an example:

    CREATE PROCEDURE TestCmdShell

    AS

    EXECUTE AS LOGIN = 'CmdShellUser'

    EXEC xp_cmdshell 'DIR C:\*.*'

    REVERT

    What I want to do is make it so that a database owner can successfully run that stored procedure but NOT be able to run the following script from SSMS.

    EXECUTE AS LOGIN = 'CmdShellUser'

    EXEC xp_cmdshell 'DIR C:\*.*'

    REVERT

    As it stands right now, the user with the database owner privs can run both. I'd like to make it so he can only run the proc but the script will fail.

    Is this possible? If so, how can I make it happen?

    Thanks for the help, folks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 22, 2011 at 10:10 am

    #1314929

    use [master]

    GO

    DENY EXECUTE ON [sys].[xp_cmdshell] TO [CmdShellUser]

    GO

    EXECUTE AS LOGIN = 'CmdShellUser'

    EXEC xp_cmdshell 'DIR C:\*.*'

    Alex S

  • April 22, 2011 at 10:43 am

    #1314946

    Thanks for the response, Alex, but won't that simply disable the ability of the proxy to use xp_CmdShell at all levels?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 22, 2011 at 12:45 pm

    #1315001

    Jeff Moden (4/22/2011)

    Thanks for the response, Alex, but won't that simply disable the ability of the proxy to use xp_CmdShell at all levels?

    So is this proxy user also a db_owner? you've used sp_xp_cmdshell_proxy_account to create it?

    In that case I believe you need to use "create credential" and then map it to cmdshelluser.

    Alex S

  • April 22, 2011 at 4:53 pm

    #1315060

    AlexSQLForums (4/22/2011)

    Jeff Moden (4/22/2011)

    Thanks for the response, Alex, but won't that simply disable the ability of the proxy to use xp_CmdShell at all levels?

    So is this proxy user also a db_owner? you've used sp_xp_cmdshell_proxy_account to create it?

    In that case I believe you need to use "create credential" and then map it to cmdshelluser.

    The proxy account user is the CmdShellUser. I have another user with only dbo privs called TestDummy. I'm trying to make it so TestDummy can execute a proc which contains a call to xp_CmdShell (has an "Execute As CmdShellUser in the proc) but can execute xp_CmdShell by itself in SSMS (or other SQL editor).

    I'll take a look at the "credentials" you've suggested.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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