April 28, 2008 at 8:07 am
I have a user who is a member of a group which has db_datareader and db_datawriter permissions to a particular db.
I have also granted that group showplan rights to the db.
when he runs the set showplan_xml on cmd, it succeeds.
if this user runs a very simple 'select top 1 * from table' select he gets 'showplan permission denied in database 'xxx'.'
in an attempt to rule out any odd windows authentication issues, Ive parsed him down to being a member of only one group in the domain, and verified that he can run the raw select without the showplan on.
If he creates a table, he can run the showplan stmt against that table.
I briefly gave the group db_owner permissions to the db, and still no dice.
according to http://msdn2.microsoft.com/en-us/library/ms189602.aspx, the only permissions needed are:
showplan access granted, and access to the object you want to run showplan against.
There is no ownership chaining issues going on, as its only a single table in a single db that the user can access.
I have also granted the showplan right to the individual user to no avail.
any suggestions?
am I missing something here? Thanks all in advance.
May 6, 2008 at 6:41 am
Anyone got anything here?
Ive done a slew of searching on the subject and havent found anything other than suggestions to cover the bases that Ive already covered.
thanks
May 6, 2008 at 2:05 pm
Some other things to try: grant (explicit) reader access to Master & TempDB.
Does this happen with the other SHOWPLAN settings or just XML?
Make sure that they have rights to View Definitions.
Check for any explicit DENY's anywhere.
Make sure that your user is really coming into the database as the DB User assigned to that Group.
Use Profiler to do a very detailed trace of all security events related to their attempt & failure. Compare it to a user that can do this sucessfully (like a dbo).
If you are still stumped then post the results of the two traces here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2008 at 1:33 pm
thanks, I appreciate the suggestions.
we are getting ready to adjust our security model in active directory, so this may be a moot point for the time being.
If I have troubles still once we get the revamped model in place, I'll check back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply