Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases on the server (SQL Server 2012 Enterprise Edition).
I understand this isn't a recommended practice, but the decision to do so was made by someone else with more authority than me.
I have no problem executing such stored procedures myself from any database on the server, but I also have sysadmin privileges and would prefer to grant this user as few permissions as possible.
Here's what I've tried:
--create procedure for purpose of testing USE [master] GO CREATE PROCEDURE dbo.sp_HelloWorld AS PRINT 'Hello World!' GO --create login for test user USE [master] GO CREATE LOGIN [DOMAIN\user] FROM WINDOWS GO --create user in master database USE [master] GO CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user] GO --create user in another database on server USE MyDB GO CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user] GO --grant execute permission on stored procedure in master database USE [master] GO GRANT EXECUTE ON dbo.sp_HelloWorld TO [DOMAIN\user] GO
I'm able to run
EXEC dbo.sp_HelloWorld myself from any database on the server without issue.
I'm also able to run the following:
USE [master] GO EXECUTE AS USER='DOMAIN\user' EXEC dbo.sp_HelloWorld REVERT
But trying to execute the stored procedure from within the context of any other database while impersonating the user:
USE MyDB GO EXECUTE AS USER='DOMAIN\user' EXEC dbo.sp_HelloWorld REVERT
Results in the following error:
Msg 229, Level 14, State 5, Procedure sp_HelloWorld, Line 1 [Batch Start Line 15] The EXECUTE permission was denied on the object 'sp_HelloWorld', database 'master', schema 'dbo'.
What am I missing?