April 16, 2010 at 10:26 am
Hi Everyone,
I've been struggling with this for two days and finally decided to seek some help. I have a .net windows application that connects to my database using integrated security. All the permissions are handled through 2 roles that everyone who uses this program has. I have one user who is having problems.
The program opens up and displays the content of one table just fine.
When she tries to add a new row it works fine, however, the 8 related tables that it also tries to create a new row in throw errors.
The errors say select permission denied. update. insert permission denied. update permission denied.
I've completely deleted her user account and recreated it which didn't work. I've given complete reader/writer access to the database with no luck. I logged onto her computer as myself and it worked just fine. If anyone has any ideas I would greatly appreciate it. Thanks.
Nathan
April 19, 2010 at 12:22 pm
i would advice to do 2 tests trying to reslove this problem
1)give this user an other name instead of the current
2)connect to sql server direct by the Sql Server Managment as this using her user name and check it there
it will maybe bring you closer to the solution
April 21, 2010 at 1:11 pm
If the commands that the application is executing are done through stored procedures then setuser='heraccount' in management studio and attempt to execute the stored procedures through management studio. If the setup is too complex to test this way then run SQL Profiler and select SP start/finish, Exceptions, and if sp's are not being used then capture SQL batch start/finish. You might be able to narrow down what exact commands are failing under her user context this way.
If you have granted the roles that she is in permissions to the stored procedures, but not DDL, and the stored procedures execute DDL, the permissions for the underlying user will be checked even though they had permissions to run the stored procedure. This is true unless the stored procedure is defined with Execute As "some other account".
April 29, 2010 at 6:21 pm
Sorry for the late reply I was out of town for awhile. So I took your advice and logged on to the server with management studio as her and found out that for some odd reason, she was only getting half of the permissions from the role I had assigned to her. I eventually "fixed" the problem by removing the role and giving her permissions to the specific objects explicitly. I'm still not sure what was going on but imagine it has to do with a messed up system table or something. Thanks for the help.
Nathan
April 30, 2010 at 7:30 am
I would suggest that it probably was not a "messed up system table", but that rather it was an unintended permissions collission. This can happen easily where it is not obvious at all because some of her permissions could be derived from several different paths. This can be especially tricky when there are deny permission.
For instance, user defined roles can be nested and active directory groups can be nested, so finding the effective permissions for a user can often be difficult. However, if you are more interested in the effective permissions for a user on a single object or at the database/server level you can execute the following:
EXECUTE AS USER = 'HERNAME'
select * from fn_my_permissions('YOUROBJECTNAME','object')
April 30, 2010 at 9:06 am
Broken ownership chains can also cause security problems.
April 30, 2010 at 1:35 pm
Toby,
I considered the conflicting permissions idea. The probelm with that is she is a member of the same domain groups as many other users. Also I control all access to that database with roles and I only ever gave her the one role when I added her account to the database. While it is certainly possible that I missed something the fact that this problem was still around after I completly deleted her account and readded her with just the one role makes me think something else was going on.
How do you check for broken ownership chains?
May 3, 2010 at 4:35 am
nathan 7372 (4/30/2010)
I considered the conflicting permissions idea. The probelm with that is she is a member of the same domain groups as many other users.
And what has this got to do with anything? Are you saying the others in this group DO have access to the database?
Also I control all access to that database with roles and I only ever gave her the one role when I added her account to the database. While it is certainly possible that I missed something the fact that this problem was still around after I completly deleted her account and readded her with just the one role makes me think something else was going on.
You're not being clear on what the role permissions are and what groups/roles she's a member of. So we can't tell if you actually followed down each security perm and verified there are no hidden "Deny"s.
How do you check for broken ownership chains?
Ownership chains are all about what schema owns the objects. A broken chain is when multiple schemas (people) own the different objects required to fulfill one request.
Simplified explanation: If I own TableA and grant Jeff perms to SELECT on it. And Jeff owns ViewB (which pulls from TableA) and Jeff grants you permissions on ViewB, but I don't grant you perms on TableA, it's entirely possible you will get an error selecting off the view.
Google "ownership chain," or look it up in Books Online.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply