I have my custom sp in my testDB database which is using dbo.sp_send_dbmail system sp from msdb. I have many users to use my custom stored procedure. But i cannot give permissions to all the users. Instead i created one login 'Admin_User' which has the following permissions granted:
1. Added user 'Admin_User' to msdb database with role 'DatabaseMailUserRole'.
2. Default security profile 'TEST_EMAIL' is added to user 'Admin_User'
Now i have a user with name 'test' in testDB database have to access my custom sp to send email. but this user doesn't have any permission in msdb database, since this user is not in msdb database.
So, when i execute the sp, i am getting following error:
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Sometimes, i used to get this error as well:
Msg 15517, Level 16, State 1, Procedure sp_send_email, Line 17
Cannot execute as the database principal because the principal "Admin_User" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I have checked this, but didnt get any answer from this article: http://msdn.microsoft.com/en-us/library/ms188304.aspx
so, how do i impersonate 'Admin_User' on msdb so that i can execute my custom sp to send email through 'test' user. I cant give any permission to test user in msdb. (Because there will be lot of users like this)
I tried to get the user_name who is executing the sp using SELECT user_name(). it returned the user name 'Admin_User'.
Please help me on this. Thanks in Advance.
CREATE PROCEDURE [dbo].[sp_email]
WITH EXECUTE AS 'Admin_User'
IF (@body IS NOT NULL AND @subject IS NOT NULL AND @recipients IS NOT NULL)
@profile_name = 'TEST_PROFILE',
@recipients = @recipients,
@subject = @subject,
@body = @body,
@body_format = 'TEXT';