Solved

Database role member validation from Trigger or SP

Posted on 2014-09-08
3
241 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
  • 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SubQuery link 4 35
SQL Select in Access 2003 3 20
Are triggers slow? 7 10
SQL Recursion schedule 13 12
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

766 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