SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do i grant permission to msdb.dbo.sp_send_dbmail ?


How do i grant permission to msdb.dbo.sp_send_dbmail ?

Author
Message
justnagendra82
justnagendra82
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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

SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1762 Visits: 1100
Did you get this resolved? If you don't mind, would you please post what you did to fix it?
hmbtx
hmbtx
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1464
If anyone is watching this post and has a solution I would like to know what it is.

Thanks,

Howard



Lowell
Lowell
SSC Guru
SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)

Group: General Forum Members
Points: 76714 Visits: 41002
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
hmbtx
hmbtx
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1464
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



Lowell
Lowell
SSC Guru
SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)

Group: General Forum Members
Points: 76714 Visits: 41002
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 :-D.

cannot undo it via the GUI, so script it instead:

USE msdb;

sp_droprolemember 'db_owner','DatabaseMailUserRole'



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
hmbtx
hmbtx
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1464
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



hkamal.infoedge
hkamal.infoedge
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 311
Add the user to DatabaseMailUserRole in msdb
hmbtx
hmbtx
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1464
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



sqlcentral2@mailinator.com
sqlcentral2@mailinator.com
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 9379
Thanks. This is the solution.

4x4 photos
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search