March 18, 2014 at 9:38 am
I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
Any suggestions on how I can accomplish the requirement?
March 18, 2014 at 10:19 am
If I change the stored proc definition to EXECUTE AS OWNER, creation succeeds:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS OWNER
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
However, when a non-sysadmin then executes this stored proc, they get this error message:
Msg 2571, Level 14, State 11, Procedure spFreeSystemCache, Line 6
User 'dbo' does not have permission to run DBCC freesystemcache.
dbo IS sa (or sa IS dbo) in this particular user database.
Any suggestions on how to accomplish this would be greatly appreciated.
March 19, 2014 at 4:29 am
If the user you're referencing in EXECUTE AS doesn't exist in the database, and have the permissions you're anticipating, combined with the user making the call not being a system admin, it won't work. So, you'd need to add the 'sa' login to the database. You can read more about the restrictions in the Books Online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2014 at 3:49 am
Grant Fritchey (3/19/2014)So, you'd need to add the 'sa' login to the database.
I don't think you can do that, can you? 'sa' is a special login which isn't allowed to have individual permissions in any database. He'd have to create a different sysadmin user and use that, I think.
(Based on testing in SQL 2012, anyway--no idea if older versions were less restrictive about the usage of sa).
March 20, 2014 at 4:15 am
paul.knibbs (3/20/2014)
Grant Fritchey (3/19/2014)So, you'd need to add the 'sa' login to the database.
I don't think you can do that, can you? 'sa' is a special login which isn't allowed to have individual permissions in any database. He'd have to create a different sysadmin user and use that, I think.
(Based on testing in SQL 2012, anyway--no idea if older versions were less restrictive about the usage of sa).
Hadn't even thought it through that far.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2014 at 3:39 am
I trust you don't want those DBCC commands to be executed on a Production instance?!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply