0

I'm having an issue giving a single user the ability to perform

select * from msdb..sysmail_profile

I get the following error:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'sysmail_profile', database 'msdb', schema 'dbo'

I've tried to grant permissions by using:

use msdb; 

grant select on msdb.dbo.sysmail_profile to [My User];

but still no avail. However, if I do the exact same grant permission command for [Public], then the user is able to perform the needed select statement, but I don't want [Public] to have access to select from that table.

I have not found any Deny's anywhere for this user, yet I am still unable to give this single user SELECT permissions. Any thoughts?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chance Manning
  • 13
  • 4
  • When you say you haven't found any Deny permissions - where have you looked? Presumably you've checked `sys.database_permissions` – Stu Sep 01 '21 at 21:26
  • Correct, I ran a script that I had found here https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database I am able to see that the User has Permission Type SELECT with PermissionState GRANT for ObjectName sysmail_profile. – Chance Manning Sep 01 '21 at 21:46
  • 2
    FWIW, `dbo.sysmail_profile` is an undocumented system table. Try granting execute on `sysmail_help_profile_sp` to retrieve mail profile info. – Dan Guzman Sep 01 '21 at 22:04
  • 1
    Ok, I know I'll regret asking, but why does a user need to view sysmail_profile ? – Mitch Wheat Sep 02 '21 at 04:12
  • This is within my test environment, to have a user to take the responsibility of dbmail. I am still relatively new with SQL and trying to get a better understanding of how permissions work down to a granular level. – Chance Manning Sep 02 '21 at 13:25

2 Answers2

0

make sure in security > logins > [myuser]> user mapping > check msdb

(if the user still does not have access try to revoke public server role because I think the default in public server role for msdb is denied)

use msdb; REVOKE select on msdb.dbo.sysmail_profile to public;

Naell
  • 1
  • 1
0

I think your context is not switched. Try with EXECUTE AS and see if the issue still persists. For me, the below code worked successfully without issues.

use master
go
CREATE LOGIN TestLogin with password = 'YOURSTRONGPASSWORD'
GO
use msdb
go
CREATE USER TestLogin for login testlogin
GO
grant select on msdb.dbo.sysmail_profile to [testlogin]
GO

EXECUTE AS user = 'Testlogin'
go
SELECT * FROM msdb.dbo.sysmail_profile
go
Revert
go
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • 1
    This seemed to work, not entirely sure what I was doing wrong, as I had granted permission to the table the same way, or so I thought. – Chance Manning Sep 02 '21 at 13:40