Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How can I find out the DB permissions of the current user?

Posted on 2013-01-30
Medium Priority
Last Modified: 2013-01-31
Hi Experts,

      I need to make sure that who ever is attempting to execute my stored procedure has the following permissions:

sysadmin fixed server role or the db_owner fixed database role

What would the query for this look like?

Question by:axnst2
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
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38836654
>>sysadmin fixed server role or the db_owner fixed database role<<
You do realize that using a sysadmin role (and even a db_owner database role) to execute a Stored Procedure is a very risky proposition, right?

Author Comment

ID: 38836879
Here it is:

IF ((IS_MEMBER ('db_owner') IS NULL OR IS_MEMBER ('db_owner') = 0)
AND (IS_MEMBER ('sysadmin') IS NULL OR IS_MEMBER ('sysadmin') = 0))
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.


Author Comment

ID: 38839967
I've requested that this question be closed as follows:

Accepted answer: 0 points for axnst2's comment #a38836879

for the following reason:

Thanks anyway!
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 38837117
Actually it's this:

IF ((IS_MEMBER ('db_owner') IS NULL OR IS_MEMBER ('db_owner') = 0)

IS_MEMBER will always return NULL for 'sysadmin', because IS_MEMBER is only for database roles.

Author Closing Comment

ID: 38839968
Thank you!
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38840394
WOW, you're welcome!
I didn't expect any points since it was already closed, I just didn't want you to get odd errors with sysadmin's being denied access when they should have it :-) .

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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