Returning a list of email profiles securely

Question

I'm writing an application that uses SQL Server to send email.

msdb.dbo.sp_send_dbmail

It all works fine, but in the Admin section of the application I'd like to give the user the option to choose which Profile to send the email from.  In order to get a list of profiles I've written a Stored Procedure that is essentially a wrapper for:

msdb.dbo.sysmail_help_profile_sp

However, this procedure requires the login of the user to have the SysAdmin fixed server role, which is not really acceptable.  Are there any other ways of determining which Profiles are set up on the server?

Thanks

Edward

Answer 1

Edward

I think you still will  have a user access to msdb system database try the below

SELECT * FROM msdb.dbo.sysmail_profile

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi Uri

That didn't work, I'm afraid - I get "The SELECT permission was denied on the object sysmail_profile ..."

Thanks

Edward

Answer 3

GRANT SELECT on sysmail_profileTO user

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi Uri

I tried that too - I get the message

Cannot find the user '*****', because it does not exist or you do not have permission.

I am running this under the sa account.  The user already exists in msdb as public and also DatabaseMailUserRole.  I've tried adding the user as db_owner and that doesn't make any difference.

Answer 5

If run this sp unser SA there should not be a problem at all

EXECUTE AS login = 'sa'

SELECT * FROM msdb.dbo.sysmail_profile

SELECT SYSTEM_USER

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Thanks Uri.  Still have a problem.  I get the following error:

"Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission."

Answer 7

it indicates that the SQL Server login that you wish to impersonate doesn’t grant you access to do the impersonation.  To grant someone rights to impersonate the SQL Server login, you could use the GRANT statement. You also need to specify the SQL Server login to which the permission is being granted in the GRANT statement. The following example grants IMPERSONATE permission on the SQL Server login sa to a SQL Server login created from the Windows user Compname\Edward

USE master;
GRANT IMPERSONATE ON LOGIN::sa to [Compname\Edward];
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 8

Hi Uri

Ok, that worked.  However, I don't know (because I'm a C# developer, not a SQL Server DBA) whether there's any material difference between doing this, and simply granting SysAdmin privileges to the humble user.  In other words, is it safe to GRANTE IMPERSONATE ON sa to a general purpose user who should only really be able to read from and write to their own database.  Bearing in mind that all I was originally trying to do was to allow the administrator of the application to select the correct mail profile for an emailing operation - itself a fairly harmless and uncontroversial operation, I'd have thought.  So why is the msdb.dbo.sysmail_help_profile_sp so inaccessible to logins that aren't part of the SysAdmin role?

Thanks

Edward

Answer 9

Granting someone the right to impersonate sa, is like giving that person the keys to safe where all the gold is.

I have a long article on my web site that will eventually give you the answer. There is no example for exactly what you ask for, but there are other examples for things in msdb. I don't outline a solution here, because on the top of my head, I am not sure what is the best in this case.

Yes, it will take some to read, learn and understand. But you should never make shortcuts with security. At least not if you care about security.

The artic is on
http://www.sommarskog.se/grantperm.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

>>>is it safe to GRANTE IMPERSONATE ON sa to a general purpose user who should only really be able to read from >>>and write to their own database

Not to sa, but you have to create another powerful login and use that login to impersonate permission...

>>> why is themsdb.dbo.sysmail_help_profile_sp so inaccessible to logins that aren't part of the SysAdmin role?

You are probably better to ask MS people as it by design.... 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 11

Hi Erland

Thanks for your input.  I will read your article and see if it holds the key to this.

Best

Edward