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?