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

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!
axnst2Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Anthony PerkinsCommented:
>>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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
axnst2Author Commented:
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
 
axnst2Author Commented:
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
 
axnst2Author Commented:
Thank you!
0
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.