Solved

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

Posted on 2013-01-30
7
175 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
[X]
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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

733 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