Solved

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

Posted on 2013-01-30
7
169 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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:
ScottPletcher 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:ScottPletcher
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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
SQl query 19 14
Creating Alerts in sql sever 2 13
Updating ms sql with special characters 8 25
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 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

20 Experts available now in Live!

Get 1:1 Help Now