sql server - How to Check MSDB for User Permissions/Roles -

i have connectionstring

 <add name="dbcon" connectionstring="data source=10.197.10.10,46512\games;initial catalog=games;user id=usera;password=;" providername="sqlmembershipprovider" /> 

before using sp_send_dbmail , through sql query, how check if usera has permissions or roles in msdb database?

it's important understand difference between sql server login (instance level object) , database user (database level object). in connection string, login named usera, might confusing. "logins" don't have permissions within database. well, not directly. it's "user" login mapped has permissions. ok, enough of that...

sql login

if login has sysadmin role membership, you'll have free reign anything, including sending mail via sp_send_dbmail. query tell if login you're connected has membership (look return value of "1"):

select is_srvrolemember('sysadmin', suser_name()) 

database user - role membership

however, suspect you're wanting know if login has user in msdb, , if so, it have permission send mail via sp_send_dbmail. run query see user in msdb (if any) login mapped , database roles user belongs to:

select l.name loginname, u.name username, r.name rolename master.sys.server_principals l join msdb.sys.database_principals u     on u.sid = l.sid left join msdb.sys.database_role_members rm     on rm.member_principal_id = u.principal_id left join msdb.sys.database_principals r     on r.principal_id = rm.role_principal_id l.name = suser_name() , r.name in ('db_owner', 'databasemailuser') 

if user member of either db_owner or databasemailuser, should able execute sp_send_dbmail. (if there explicit deny execute on sp_send_dbmail [yourdbuser], user not have privilege.)

if user dbo, owns msdb (not likely--system databases typically owned sa login), , database owner, can perform activity on db.

database user--individual grants

lastly, there 1 other possibility can think of: database user in msdb might not in database roles, explicitly have been given privilege execute sp_send_dbmail follows:

grant execute on sp_send_dbmail [yourdbuser] 

this show permissions have been explicitly granted or denied database user:

use msdb go  select * fn_my_permissions(null, 'database'); go