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
Solved

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

Posted on 2013-01-30
7
173 Views
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?

Thanks!
0
Comment
Question by:axnst2
7 Comments
 
LVL 3

Expert Comment

by:Nalinkumarbalaji
ID: 38836220
0
 
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?
0
 

Author Comment

by:axnst2
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))
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:axnst2
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!
0
 
LVL 69

Accepted Solution

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


IF ((IS_MEMBER ('db_owner') IS NULL OR IS_MEMBER ('db_owner') = 0)
AND (IS_SRVROLEMEMBER ('sysadmin') IS NULL OR IS_SRVROLEMEMBER ('sysadmin') = 0))


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

Author Closing Comment

by:axnst2
ID: 38839968
Thank you!
0
 
LVL 69

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 :-) .
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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