Solved

Database role member validation from Trigger or SP

Posted on 2014-09-08
3
249 Views
Last Modified: 2014-09-09
I Need to validate if a system user has the "Database Mail User role" in the msdb database but I need to call it from either a stored procedure or trigger from another database, and of course the USE [DATABASE] is not allowed in SP or triggers. If I execute  select IS_ROLEMEMBER ('DatabaseMailUserRole' ) as permission in the msdb database it works fine returning a 1 or 0. any help would be greatly appreciated.
0
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40310374
I take it you mean "IS_MEMBER"?

DECLARE @is_member bit
EXEC sp_executesql N'USE msdb; SELECT @is_member = IS_MEMBER(''DatabaseMailUserRole'')', N'@is_member bit OUTPUT', @is_member OUTPUT
--SELECT @is_member
0
 

Author Comment

by:skull52
ID: 40312463
Thanks Scott, that is exactly what I needed.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40312483
Great, glad it helped!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question