Stored Procedures - Execute Permissions - proc with dbcc command

Question

Stored Procedures - Execute Permissions - proc with dbcc command

Steve Malley

Ten Centuries

Points: 1204
More actions
November 7, 2011 at 2:38 pm

#262504

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?

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

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

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!

Steve Malley Ten Centuries Points: 1204 More actions · Answer 2

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.

Steve Malley Ten Centuries Points: 1204 More actions · Answer 3

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.

Lowell SSC Guru Points: 323547 More actions · Answer 4

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!