Sql-server – How to grant permission to execute stored procedure in master database from another database

permissionsSecuritysql serverstored-procedures

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?

Best Answer

What am I missing?

The DOMAIN\user login should be able to run dbo.sp_HelloWorld with it's corresponding user in the MyDB context.

The EXECUTE AS USER command is the reason for the error being returned.

Execute as user documentation

Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.

Important part: the scope of impersonation is restricted to the current database.

Which is why the permissions from other databases such as master are not applied when using EXECUTE AS USER.

You need to use EXECUTE AS LOGIN if you want the correct login / user mapping

Execute as login documentation

Specifies the execution context to be impersonated is a login. The scope of impersonation is at the server level.

USE MyDB
GO
EXECUTE AS LOGIN ='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

Result

Hello World!
Related Question