Granting a user execute on DBCC FREEPROCCACHE (ONLY)

_{Thursday, January 23, 2014 3:04 PM}

would anyone know how to grant user 'X' execute permission on  DBCC FREEPROCCACHE ONLY

eddy.a

_{Thursday, January 23, 2014 3:48 PM ✅Answered | 1 vote}

would anyone know how to grant user 'X' execute permission on  DBCC FREEPROCCACHE ONLY

eddy.a

you cannot do it directly

you need to wrap it into a stored procedure which executes under a different principal and grant execute on that one

Andreas Wolter (Blog | Twitter)
MCM - Microsoft Certified Master SQL Server 2008
MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.andreas-wolter.com | www.SarpedonQualityLab.com

_{Thursday, January 23, 2014 3:19 PM | 1 vote}

Hello Eddy,

See DBCC FREEPROCCACHE (Transact-SQL) => Permissions: Requires ALTER SERVER STATE permission on the server.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, January 23, 2014 10:18 PM}

That does not sound like a good idea to me. DBCC FREEPROCCACHE is a fairly hefty operation. What is real problem?

Else the method would be put the command in a stored procedure, which you sign with a certificate. From this certificate you create a login (which is not a realy login that can log in) that you grant ALTER SERVER STATE. For a detailed description of this technique, see this article on my web site:
http://www.sommarskog.se/grantperm.html

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se