1

The documentation says that, for SQL Server, DBCC DROPCLEANBUFFERS needs the following permissions:

Requires ALTER SERVER STATE permission on the server

Yet when I am granted that, I still don't have permissions to run DBCC DROPCLEANBUFFERS.

I get:

User 'MyLoginHere' does not have permission to run DBCC DROPCLEANBUFFERS.

Are there other, hidden permissions needed to grant permissions to run this?

1 Answer 1

4

I suspect this is a bug rather than a documentation issue since it works in SQL Server 2022. I successfully ran the script below in SQL 2022 but it fails in older versions with "User 'guest' does not have permission to run DBCC DROPCLEANBUFFERS.".

USE master;
CREATE LOGIN Repro WITH PASSWORD='sdfI#(#8403';
GRANT ALTER SERVER STATE TO Repro;
GO
EXECUTE AS LOGIN = 'Repro'
GO
DBCC DROPCLEANBUFFERS;
GO
REVERT;
GO

A work-around to allow less privileged users to execute DBCC DROPCLEANBUFFERS is to wrap the command in a proc and sign it based on a certificate login with the needed permissions. This method is better than granting ALTER SERVER STATE if only DBCC DROPCLEANBUFFERS is needed. See Erland's Packaging Permissions in Stored Procedures article for a complete discussion of this technique.

USE master;
CREATE CERTIFICATE sysadmin_cert
   ENCRYPTION BY PASSWORD = '<encryption-password>'
   WITH SUBJECT = 'Confer sysadmin privileges';
GO
-- Create a login for the certificate.
CREATE LOGIN sysadmin_cert_login FROM CERTIFICATE sysadmin_cert;
ALTER SERVER ROLE sysadmin
    ADD MEMBER sysadmin_cert_login;
GO
CREATE PROC dbo.usp_DROPCLEANBUFFERS
AS 
DBCC DROPCLEANBUFFERS;
GO
ADD SIGNATURE TO dbo.usp_DROPCLEANBUFFERS BY CERTIFICATE sysadmin_cert WITH PASSWORD='<encryption-password>';
GO
CREATE USER Repro; --in master database
GRANT EXECUTE ON dbo.usp_DROPCLEANBUFFERS TO Repro;
GO
EXECUTE AS LOGIN = 'Repro'
GO
EXECUTE dbo.usp_DROPCLEANBUFFERS;
GO
REVERT;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.