Stored Procedures - Execute Permissions - proc with dbcc command

  • If I create a stored procedure with 'dbcc freeproccache' in it, and then grant execute on that stored procedure to a specific user account, the grantee should be able to execute even a dbcc command, since the person who created the proc is a sysadmin. Is this correct?

  • Steve Malley (11/7/2011)


    If I create a stored procedure with 'dbcc freeproccache' in it, and then grant execute on that stored procedure to a specific user account, the grantee should be able to execute even a dbcc command, since the person who created the proc is a sysadmin. Is this correct?

    you need to build the PROC with EXECUTE AS in order to use the creators credentials, otherwise the calling users credentials are used;

    CREATE procedure pr_CallBoostedSecurityProcess

    WITH EXECUTE AS 'superman'

    AS

    BEGIN

    dbcc freeproccache

    END

    now, typically, folks call dbcc freeproccache because of performance issues, but it's really forcing the optimizer to build new plans...i'd guess that statistics might be stale, and it's doubtfull that every single plan needs to be dropped...

    you might want to post what the issue is that is getting you to feel that freeproccache is the answer.

    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!

  • Thank you, Lowell.

    There should be no reason to run dbcc commands from an app, but we have found that we get weird results on proc compiles post-deploy, unless everything gets recompiled. [Maybe we can convince devs that, "There is no dbcc command."] We are an always-up shop, so, sobeit.

    The plan is to run this after deployments to guarantee that all plans are recompiled.

  • I did as you suggested, Lowell, and the freeproccache ran fine. Unfortunately, there was an Db sendmail further on that failed. I would have thought that both these commands would have been covered by the 'execute as' authority, but no such luck. I wound up creating a SQL Agent job [which I hate to do] to get the result I needed.

  • Steve Malley (11/16/2011)


    I did as you suggested, Lowell, and the freeproccache ran fine. Unfortunately, there was an Db sendmail further on that failed. I would have thought that both these commands would have been covered by the 'execute as' authority, but no such luck. I wound up creating a SQL Agent job [which I hate to do] to get the result I needed.

    well the sendmail issue is where the calling user needs to exist as a user in the msdb database and also be part of the msdb's role "DatabaaseMailUserRole"

    glad you got a solution in place that is working for you!

    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!

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

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