sql 2k5 effective permissions list not containing the explicit permissions


I have this sql 2k5 db and noticed something weird: I have this new user A in the role_A and give the role_A excecute permission on the stored procedure SP, also gave user A execute permission in on the SP. Checked Effective permssions for user A on SP and there is no execute. I do not need to mention that user A cannot execute SP !! How can that be? What am I missing?

This is critical

Thank you in advance Adrian

more ▼

asked Oct 16, 2009 at 11:16 PM in Default

Adrian gravatar image

31 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Please show the code you used.

Have you checked for a DENY for the user or a role the user is in on that object?

more ▼

answered Oct 17, 2009 at 12:40 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82


thank you for getting back to me. it is a new user and a new role and they do not have any deny.

Thank you, Adrian
Oct 17, 2009 at 02:11 PM Adrian
Are there any DENY's on public/guest?
Oct 18, 2009 at 04:18 PM RBarryYoung
Also, check the explicit permissions for the user to see if your GRANT shows up there.
Oct 18, 2009 at 04:19 PM RBarryYoung
Yes, the Grant does show up, that is why I am puzzled, there is an execute granted in explicit which is not refleted in the effective ... it seems as serious bug to me. Thank you
Oct 18, 2009 at 11:15 PM Adrian
(comments are locked)
10|1200 characters needed characters left

can the user access the data that the SP touches?

more ▼

answered Oct 21, 2009 at 04:41 PM

alen teplitsky gravatar image

alen teplitsky
21 2 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2009 at 11:16 PM

Seen: 2938 times

Last Updated: Oct 18, 2009 at 09:13 AM