Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do i grant permission to msdb.dbo.sp_send_dbmail ? Expand / Collapse
Author
Message
Posted Monday, March 30, 2009 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 27, 2009 10:52 AM
Points: 1, Visits: 8
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.

CODE:

CREATE PROCEDURE [dbo].[sp_email]
(
@recipients VARCHAR(MAX),
@subject VARCHAR(MAX),
@body VARCHAR(MAX)
)
WITH EXECUTE AS 'Admin_User'
AS
BEGIN
IF (@body IS NOT NULL AND @subject IS NOT NULL AND @recipients IS NOT NULL)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST_PROFILE',
@recipients = @recipients,
@subject = @subject,
@body = @body,
@body_format = 'TEXT';
end
END
Post #686387
Posted Monday, August 29, 2011 3:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 327, Visits: 853
Did you get this resolved? If you don't mind, would you please post what you did to fix it?
Post #1167221
Posted Tuesday, December 27, 2011 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 1:28 PM
Points: 159, Visits: 1,011
If anyone is watching this post and has a solution I would like to know what it is.

Thanks,

Howard



Post #1227109
Posted Tuesday, December 27, 2011 4:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 12,880, Visits: 31,796
the solutions is fairly simple, but it varies a bit based on your specific setup. it all boils down to the group or login needs to be part of the built inDatabaseMailUserRole in msdb
lets assume this scenario;
a Windows Login [mydomain\Clarkkent or SQL Login [ClarkKent] is being used to get to the PRODUCTION database, and you want to let that user use the procedure that uses dbmail. that login has a matching USER in the PRODUCTION database we talked about.
you would go to the msdb database, and add that user to the msdb database also.

USE msdb;
--add our user
CREATE USER ClarkKent FOR LOGIN ClarkKent;
--give this user rights to use dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'

now if we know ClarkKent is getting his authorization from a windows group, then you add that windows group as a user, and add that group to the same role;
USE msdb;
--add our user via a group we know he is in
CREATE USER 'mydomain\BusinessGroup' FOR LOGIN 'mydomain\BusinessGroup';
--give this GROUP rights to use dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'mydomain\BusinessGroup'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1227142
Posted Tuesday, December 27, 2011 6:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 1:28 PM
Points: 159, Visits: 1,011
Lowell:

Thank you for the detailed instructions. I have three questions if you have the time to answer them but if your time is limited an answer to question #1 would be greatly appreciated.

Question #1:
I am using SQL authentication and not Windows authentication. How would this change your instructions?

Question #2:
I have dbmail working but I noticed that the profile that I am using in the procedure is not public and not the default profile. Could the solution be as simple as changing this profile to Public and Default profile?

Question #3:
In attempting to resolve the issue I used SSMS and in Database > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner. Obviously this did not solve my problem. However, now I cannot uncheck db_owner. It is grayed out. Do you know how I can uncheck db_owner?

Thanks

Howard



Post #1227149
Posted Tuesday, December 27, 2011 7:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 12,880, Visits: 31,796
hmbtx (12/27/2011)
Lowell:

Thank you for the detailed instructions. I have three questions if you have the time to answer them but if your time is limited an answer to question #1 would be greatly appreciated.

Question #1:
I am using SQL authentication and not Windows authentication. How would this change your instructions?

Same solution. add the SQL login as a user to msdb, and then add the user to the built in role i identified. my first code example was a sql login, but the solution is the same.

Question #2:
I have dbmail working but I noticed that the profile that I am using in the procedure is not public and not the default profile. Could the solution be as simple as changing this profile to Public and Default profile?

making one of your profiles a default really only has the affect of allowing you to eliminate one of the parameters for the sp_send_dbmail call; while i do have a default profile set up, all my mail calls explicitly identify the profile name, because you just don't know that someone might drop the profile or change the default sometime in the future. so my answer is good idea, but don't code around an assumed default profile.

Question #3:
In attempting to resolve the issue I used SSMS and in Database > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner. Obviously this did not solve my problem. However, now I cannot uncheck db_owner. It is grayed out. Do you know how I can uncheck db_owner?

Thanks

Howard

hehe, that's a GUI bug... i just duplicated your steps and see the issue .

cannot undo it via the GUI, so script it instead:
USE msdb;

sp_droprolemember 'db_owner','DatabaseMailUserRole'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1227153
Posted Wednesday, December 28, 2011 10:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 1:28 PM
Points: 159, Visits: 1,011
Lowell:

Thanks again for your detailed response to my questions.

#1:
I executed the following script but it did not drop db_owner from DatabaseMailUserRole.
Do I need to stop and start SQL Server for this action to take place?

USE msdb;
sp_droprolemember 'db_owner','DatabaseMailUserRole'

#2:
I executed the following script (in my test I substituted ClarkKent with a real user name):

USE msdb;
--add our user
CREATE USER ClarkKent FOR LOGIN ClarkKent;
--give this user rights to use dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'

In my stored procedure I added with execute as 'ClarkKent'

I made the profile public and default profile.

The script still fails when run as a job.

Can you tell me if I have missed a step or two?

Thanks

Howard



Post #1227470
Posted Friday, December 30, 2011 1:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:12 PM
Points: 192, Visits: 199
Add the user to DatabaseMailUserRole in msdb
Post #1228550
Posted Thursday, January 5, 2012 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 1:28 PM
Points: 159, Visits: 1,011
Lowell:

I have more information on
USE msdb;
sp_droprolemember 'db_owner','DatabaseMailUserRole'
not working and possibly a bug as we previously discussed on this posting.

I sent the following question to microsoft:

Using SSMS in Databases > System Databases > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner.

However, now I cannot uncheck db_owner. It is grayed out.

I then used the following T-SQL commands:
use msdb;
sp_droprolemember 'db_owner', 'DatabaseMailUserRole;

and although the message returned was 'Command(s) completed successfully' the role was not dropped.

Do you know how I can uncheck db_owner?

Microsoft's response is as follows:

Posted by Microsoft on 1/5/2012 at 2:54 AM
This is not a real issue. In the repro steps provided by the customer, he is making role the owner of db_owner schema. Ownership of a schema can be changed by "ALTER AUTHORIZATION" T-SQL not by sp_droprolemember.
In order to change the ownership of a schema, one will have to open the properties dialog of a user/role which should be made the owner of the schema and then select this schema "db_owner" in the Owned schemas box and click Ok

What is your opinion of their answer?
Do you think I should post this as a separate question on this forum?

Thanks

Howard



Post #1230785
Posted Wednesday, March 20, 2013 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:13 AM
Points: 122, Visits: 9,002
Thanks. This is the solution.

4x4 photos
Post #1433167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse