Solved

Database role member validation from Trigger or SP

Posted on 2014-09-08
3
225 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:
ScottPletcher 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:ScottPletcher
ID: 40312483
Great, glad it helped!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now