September 29, 2005 at 8:16 am
I seem to be getting interesting behaviour from one of the local databases where I work.
A user requested access to a table to be able to update a lookup list used by a program. This seemed easy enough.
So, I granted her insert and update permissions, over and above the public and explicit select she already had.
On insert, the server returned a failure. The trace of attempts showed that it was actually attempting the insert at the time of failure.
So, I checked the relevant table, for all explicit denies, and there are none.
Even with the user removed from the role she was normally in, with explicit permissions, granted by command line, access was still denied.
By using the PERMISSIONS directive, the bit fields expressly showed she did not have insert rights, despite being explicitly added.
I may be completely overlooking something obvious here, but after checking role memberships, denies and performing grants in several different ways, I am currently stumped.
Anyone able to enlighten me on this matter?
September 29, 2005 at 9:38 am
Please post the actual message received on insert.
Are there triggers on the table inserting/updating/selecting on other tables that the user does not have permissions to?
September 29, 2005 at 10:09 am
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object '', database '', owner 'dbo'.
The and fields are rewritten from the originals, but the rest of the message is as it came out.
In other words, the usual message you get when you don't have the privileges to insert to a DB.
There are no triggers, views or constraints on this table that would affect an insert (no triggers at all in fact).
I get this, even with ad hoc creation of an unpopulated table, then granting the requisite privs, both through EM and the direct commands using SQL QA.
September 29, 2005 at 3:57 pm
Execute this query
select *
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where table_Name = 'mytable'
Substitute your tablename.
This will show you privileges for users on that table.
I'm not sure why your seeing this behavior.
I have seen where enterprise manager does not properly display the privileges and such.
September 30, 2005 at 1:08 am
Are you sure you are granting the privileges to the correct account? The one that she uses to log on? E.g. when switching from SQL authentication to Windows (domain) authentication, there can be two accounts for every user : user_name and domain\user_name. Sometimes users log on as some other user, too... for various strange reasons, like "well, when I came here she showed me how to log on, so I wrote down everything (=including her user name and password) and I'm using it since that time". Make sure how she is logging on and try to capture her activity with Profiler, if it helps.
September 30, 2005 at 2:29 am
Hi Vladan.. I mentioned in the original post that a trace showed the progress of the queries (that's where profiler came in). That did include the database user name and the Domain login.
I only use Domain authenticated accounts here, so there's no crossover to SQL Server auth possible (double checked this to make sure).
Logins are correct, and as I noted, the binary permissions mask for the account is not amended correctly by a grant (by explicit checking using the permissions directive).
Thanks for the reply though. Anything's welcome if it nudges me in the right direction. I've half a feeling I'm missing something simple on this, but can't for the life of me work out what it is.
September 30, 2005 at 2:41 am
Hi Ray M.
That query returns information that the account is granted permissions to update, insert and select on that table (as it shows in EM). However, this does not match with the PERMISSIONS statement return bitmap on the explicit object (which shows that the user doesn't have delete or update).
And it seems that PERMISSIONS shows the state the the database uses for checking to see what a user's actual privileges are.
Thanks for the feedback on this though! I will cheer loudly (even in this busy office) for anyone that can solve the conundrum!
As a little extra info, this is a database I've recently taken over, and am slowly auditing my way through it.
It started off as a 6.5 data file, and has gone through several upgrades.
The issue seems to be long standing, and was known about quite a while ago, but the DBA at the time didn't fix it as it wasn't seen as a huge issue.
As I tend not to be too content when things don't do what I tell them to (in the computer sense), I want to seriously house train this database until it's not just house trained, but also jumping through the correct hoops.
September 30, 2005 at 9:49 am
Just a thought, have you tried dropping the user from the database and then adding her back? Sometimes there is a disconnect between the Logins setup for SQL-Server and the Users for a particular database, especially if an upgrade occurred.
Steve
September 30, 2005 at 10:14 am
Created a new user to test this, and get exactly the same behaviour..
It also behaves exactly the same way on the same DB backed up and restored on a different server.
No other databases on the restore server behave in this fashion, just that one, so it seems as though the issue is held in the database files themselves, and the way the security mechanism works on objects in that datafile.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply