red herring error "The user does not have permission to perform this action"

Question

When running a stored procedure, we're getting the error 297

"The user does not have permission to perform this action"

This occurs during times of heavy load (regularly, when a trim job is running concurrently). The error clears up when the service accessing SQL Server is restarted (and very likely the trim job is finished as well), so it's obviously not a real permissions problems. The error is reported on a line of a stored procedure which access a function, which in turn accesses dynamic management views.

What kind of situations could cause an error like this, when it's not really a permissions problem?

Might potentially turning on trace flag 4616 fix this, as per this article? I'd like to be able to just try it, but need more info. Also, I'm baffled by the fact that this is an intermittent problem, only happening under periods of high activity.

I was trying to reproduce this same error in other situations (that were also not real permissions problems), and I found that when running this on SQL Server 2005 I do get the permissions problem:

select * from sys.dm_db_index_physical_stats(66,null,null, null, null)

(66 is an invalid DBID.)

However, we're not using dm_db_index_physical_stats with an incorrect DBID. We ARE using dm_tran_session_transactions and dm_tran_active_transactions, but they don't accept parameters so I can't get the error to happen with them. But I was thinking perhaps that the issue is linked.

Thanks for any insights.

Answer 1

I know this sounds wierd but try setting the Database property "AutoClose" to "true". This is just a guess of course. Also, if this is a production environment, you shouldn't leave it that way.

Answer 2

If this occurs only during periods of heavy activity maybe you can run Profiler and watch for what locks are being held.

Also is this always being run the same way? For example is it run as a SQL Agent Job? or are you sometimes running manually and sometimes running it as a job. My thinking is maybe it is running as diff. users at different times.

Maybe also take a look at this Blog Post

Answer 3

Would it be related to concurrency issues?

For example, the same data being processed or a global temp table being accessed? If so, you may consider sp_getapplock

And does each connection use different credentials with a different set of permissions? Do all users have GRANT VIEW SERVER STATE TO xxx?

Finally, and related to both ideas above, do you use EXECUTE AS anywhere that may not be reverted etc?

Completely random idea: I've seen this before but only when I've omitted a GO between the end of the stored proc definition and the following GRANT statement. So the SP tried to set it's own permissions. Is it possible that a timeout or concurrency issue causes some code to run that wouldn't normally?

Answer 4

Thanks everyone for your input. What I did (which looks like it's fixed the problem for now), is alter the daily trim job. It now waits substantially longer between deletes, and also deletes a much smaller chunk of records at a time.

I'll update this later on with more info as I get it.

Thanks again.