How to Check MSDB for User Permissions/Roles

Question

I have a 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 and through a sql query, how to check if UserA has any permissions or roles in the msdb database?

Answer 1

It's important to understand the difference between a SQL Server login (instance level object) and a database user (database level object). In your connection string, the login is named UserA, which might be confusing. "Logins" don't have permissions within a database. Well, not directly. It's the "user" that the login is mapped to that has permissions. Ok, enough of that...

SQL Login

If your login has sysadmin role membership, you'll have free reign to do anything, including sending mail via sp_send_dbmail. This query will tell you if the login you're connected as has that membership (look for a return value of "1"):

SELECT IS_SRVROLEMEMBER('sysadmin', SUSER_NAME())

Database User - Role Membership

However, I suspect you're wanting to know if your login has a user in msdb, and if so, does it have permission to send mail via sp_send_dbmail. Run this query to see what user in msdb (if any) that your login is mapped to and any database roles that user belongs to:

SELECT l.name LoginName, u.name UserName, r.name RoleName
FROM 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
WHERE l.name = SUSER_NAME()
AND r.name IN ('db_owner', 'DatabaseMailUser')

If the user is a member of either db_owner or DatabaseMailUser, it should be able to execute sp_send_dbmail. (If there was an explicit DENY EXECUTE ON sp_send_dbmail TO [yourDBuser], then the user would not have that privilege.)

If the user is dbo, then it owns msdb (not likely--system databases are typically owned by the sa login), and as the database owner, it can perform just about any activity on the db.

Database User--Individual Grants

Lastly, there is one other possibility I can think of: the database user in msdb might not be in any database roles, but could explicitly have been given the privilege to execute sp_send_dbmail as follows:

GRANT EXECUTE ON sp_send_dbmail TO [yourDBuser]

This will show you what permissions have been explicitly GRANTED or DENIED to your database user:

USE msdb
GO

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
GO