SQLTeam.com | Weblogs | Forums

Object not found or you do not have permission

I have an application that a user logs into using Windows authentication to connect to the database. Once in the application they are running a process that imports a file into the database. We have 3 users that have the appropriate permissions to perform this job function. This week and existing user of the application needed to be able to run the process. I had to give her the bulk_admin role initially and then when she ran the process she received:

"Database Error 1088: Cannot find the object "xxxxx" because it does not exist or you do not have permissions. "

Her login is identical to the other three users that are able to run the process. They are all part of the same database role which has permission to the table object being referenced in the error. If I give the user the ddladmin role for the database where the table resides then she is able to run the process. I am reluctant to do this though since none of the other three users have this database role.

Any ideas?

What kind of object is "xxxxx"? And where does it "live"? Can the other users play against this exact same object by executing the exact same code as the user that can't? And I do mean "exact".

The object is a table. The users are executing the same stored procedure.

  1. does the object exist?
  2. does the application use integrated authentication in the connection string?
  3. is the app using stored proxedures to interact with object

The object obviously exists because as I stated other users can execute the stored procedure. They are using Windows authentication to connect.

The only reason I can think of is if the user has explicitly been denied access or some group the user belongs to has been denied.

I've also seen things go a bit haywire for no apparent reason. What may need to be done is to drop the user from the database, drop the user login, and rebuild both.

duh haha. this is what you get when you cant sleep and wake up at 4am and respond to sql questions.

I think user is part of another sql group might be the correct answer

Thanks Jeff. unfortunately there are no other groups she is a part of that are being denied permission. I was reluctant to drop the account but I will probably try it on our test server to see if it works.

I tried dropping and recreating the account but it didn't change anything.

Is the object in the default dbo schema - and is the users default schema defined as dbo?

If the users default schema is the same as her username - and the stored procedure is not schema qualifying the object - then this user would not be able to find it and you would see the above error even if the user has been granted access to that schema.

You should also verify the schema owner is actually dbo - and not something else. If the schema is owned by something other than dbo (e.g. the same as the schema) then you can and will have ownership chaining issues.

I am zeroing in on the problem. A while back we had a domain name and username change. We had to create new usernames. The three users that I said were able to run the process without the db_ddladmin database role actually had it on their old account. Now I just need to sort out these accounts and do some housekeeping that I have wanted to do for a while.