July 20, 2010 at 4:02 pm
I have something strange which just started midway through the day. Suddenly my users are having errors showing up indicating they have been denied permission to a given object. However, they were able to use this same object a couple hours earlier.
No changes to permissions were made today. When I check, I see SQL has the user logging in, so they are connecting. Additionally, the user I am testing with has explicit permissions on the object in SQL, while the application is still reporting deny permission.
We are using virtual machines for both the SQL server, and the local user PC.
Does anyone have a direction to point me in?
I have never encountered anything like this before, usually there is some place preventing the user from hitting the SQL box itself. Here everything I check seems to be fine, and unchanged from earlier in the day.:crazy:
July 20, 2010 at 4:19 pm
Somebody removed the permission, either by accident or on purpose.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 20, 2010 at 4:22 pm
That was my first thought. However, I did look at all the user IDs and the permissions for specific objects which were failing. In SQL the user does have permissions. I have even attempted giving the specific user, rather than a windows group permission, and still from the user side, permission is denied.
Is there anyway the EM for SQL would show the user permissions incorrectly?
July 20, 2010 at 4:25 pm
EM would be correct, assuming you've got a refreshed view of it.
I'm a little confused though. Is the user that you are testing with the same user that the application is connecting with? If so, then you'll need to open up SQL Profiler and grab the exact query the application is running as well as the exception. Verify that the database name is correct, object owner is correct, etc...
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 20, 2010 at 4:30 pm
any DENY permissions override grants. Have the users been added to another group which has DENY permissions on the object?
---------------------------------------------------------------------
July 20, 2010 at 4:33 pm
I agree. I have done these same things. What is set up is an excel file which uses ODBC to open a view on SQL. At 3:00 PM or so, the user opened the report and used it fine. At 4:00 PM the same user, same machine, and same report fails.
I find no changes to the user logon, security, permissions, nor the report.
I know the user was having issues with the VM they were using after 3:00 PM. What I don't have any information on is if anything in the configuration of the VMs or network changed during that time.
A month or so ago, there was a similar issue when the VM admin updated one of the VM devices IP address. However, after a reboot of the SQL box, to clear out the DNS information, everything worked fine again.
I have tried the same today, and have no results.
This is happening to most but not all users. On those who are having this issue, even reports which are given rights to the entire user base are giving the permission deny message.
July 20, 2010 at 4:37 pm
George, if I take the object in question in the EM, and list it's specific permissions the only listed permissions are select permissions for the correct set of IDs.
This is very odd.
July 20, 2010 at 4:38 pm
please post the exact error message received
---------------------------------------------------------------------
July 20, 2010 at 4:42 pm
The error comes from the excel report, 'Select permission denied on object DB.Table'
It is the standard error message on the excel side when a user is actually denied permissions.
July 20, 2010 at 5:00 pm
The error is not 'Select permission denied on object DB..Table'? (two dots) Is the owner of the table specified correctly, i.e is it dbo?
to confirm it is a SQL permissions issue could you give the user dbo (temporarily) and see if it works. If that does not help the error is somewhere else, though it certainly looks permissions related.
Just get the users to confirm nothing has been changed their end (you never know)
---------------------------------------------------------------------
July 20, 2010 at 5:08 pm
A couple things:
- Windows auth or sql auth?
- Are you sure the Excel connection is hitting the right VM? Could there be multiple copies out there? Or a naming issue?
- You are sure the login is connecting to SQL (trace/profiler) and that the permissions is at the db/object level? I'm not sure how Excel error messages work, so I want to be sure this is what is working.
- Can you connect with these user credentials using Access or SSMS or SQLCMD?
July 21, 2010 at 8:17 am
Steve..
We are using Windows auth.
The VM staff tell me nothing has been changed in several weeks with the VMs etc. I have verified the server name and the server IP are still the same, and that Excel is still pointing to the same location.
I have triple verified the user ID connects to SQL, with the correct name, and starts a SPID. The object has that user specifically granted select permission. There are no explicit deny permissions on the object.
Now that is a strange thing, a user unable to connect using ODBC in excel is able to use Access to connect. Do you have any thoughts on why that would be?
July 21, 2010 at 8:46 am
george sibbald (7/20/2010)
The error is not 'Select permission denied on object DB..Table'? (two dots) Is the owner of the table specified correctly, i.e is it dbo?to confirm it is a SQL permissions issue could you give the user dbo (temporarily) and see if it works. If that does not help the error is somewhere else, though it certainly looks permissions related.
Just get the users to confirm nothing has been changed their end (you never know)
George, here is the exact error message.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permission denied on object 'Table name', database 'DB_Reports', ownder 'dbo'.
The table name is obviously different depending on the report being used.
July 21, 2010 at 9:22 am
Have you run a Profiler trace to ensure they're accessing the same object? Turned on the appropriate events to see exactly what error SQL Server is throwing (in case it's not being reported by Excel correctly). Is there more than one database with the same objects? If so, could the user be pointed at the wrong one?
K. Brian Kelley
@kbriankelley
July 21, 2010 at 9:32 am
I am confused. I would follow Brian's suggestion and trace the excel and Access connections, trying to select from the same object.
There has to be something weird going on? Case sensitivity for objects? maybe a separate owner for the same named object? Is one using two (or 3/4) part naming?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply