Solved

Database role member validation from Trigger or SP

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

785 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